17,377
社区成员
发帖
与我相关
我的任务
分享
{"emplid":"A2001","Name":"张三","sip":"566669@qq.com","ename":"ZHANGSAN"}
{"emplid":"A2002","Name":"李四","sip":"66688899@qq.com","ename":"LISI"}
{"emplid":null,"Name":null,"sip":null,"ename":null} --查不到数据
--1.创建测试表
with tmp as
(
select '{"emplid":"A2001","Name":"张三","sip":"566669@qq.com","ename":"ZHANGSAN"}' str from dual union all
select '{"emplid":"A2002","Name":"李四","sip":"66688899@qq.com","ename":"LISI"}' str from dual union all
select '{"emplid":null,"Name":null,"sip":"null","ename":null}' str from dual
)
--2.sql实现
select str, substr(str,instr(str,',',1,2)+8,instr(str,',',1,3)-instr(str,',',1,2)-9) from tmp
select '{"emplid":"A2001","Name":"张三","sip":"566669@qq.com","ename":"ZHANGSAN"}',
substr('{"emplid":"A2001","Name":"张三","sip":"566669@qq.com","ename":"ZHANGSAN"}',
instr('{"emplid":"A2001","Name":"张三","sip":"566669@qq.com","ename":"ZHANGSAN"}',
',',
1,
2) + 8,
instr('{"emplid":"A2001","Name":"张三","sip":"566669@qq.com","ename":"ZHANGSAN"}',
',',
1,
3) - instr('{"emplid":"A2001","Name":"张三","sip":"566669@qq.com","ename":"ZHANGSAN"}',
',',
1,
2) - 9)
from dual
select '{"emplid":"A2001","Name":"张三","sip":"566669@qq.com","ename":"ZHANGSAN"}',substr(str,instr('{"emplid":"A2001","Name":"张三","sip":"566669@qq.com","ename":"ZHANGSAN"}',',',1,2)+8,instr('{"emplid":"A2001","Name":"张三","sip":"566669@qq.com","ename":"ZHANGSAN"}',',',1,3)-instr('{"emplid":"A2001","Name":"张三","sip":"566669@qq.com","ename":"ZHANGSAN"}',',',1,2)-9) from dual
select json字段,substr(str,instr(json字段,',',1,2)+8,instr(json字段,',',1,3)-instr(json字段,',',1,2)-9) from 表名称