最近写接口自动化,验证数据时遇到了Oracle中的CLOB类型,就此了结到Oracle的substr和instr函数,简单做以下整理:
例 Table-Vege
ProID | JsonString |
Products | CLOB |
<CLOB>内包含以下json串
{
"ProductTitle": "D11",
"Products": [{
"name": "tomatos",
"id": "P01",
"desc": "red",
"Price": "10.00"
},
{
"name": "potato",
"id": "P02",
"desc": "yellow",
"Price": "15.00"
},
{
"name": "onion",
"id": "P03",
"desc": "purple",
"Price": "12.00"
},
{
"name": "cucumber",
"id": "P06",
"desc": "green",
"Price": "10.00"
}
]
}
1.截取id
select substr(to_char(jsonstring),instr(to_char(jsonstring),"id",1,1)+5,3) from vege;
select substr(to_char(jsonstring),instr(to_char(jsonstring),"id",1,2)+5,3) from vege;
select substr(to_char(jsonstring),instr(to_char(jsonstring),"id",1,3)+5,3) from vege;
select substr(to_char(jsonstring),instr(to_char(jsonstring),"id",1,4)+5,3) from vege;
results:
P01,P02,P03,P04
2.substr函数
substr(string,StartPosition,CharacterLenth)
string:表达式,字符串
StartPosition:截取的位置-位于该字符的什么位置截取
CharacterLenth:截取的长度
eg:
substr("abcd",2,3) ==>bcd
3.instr函数
用途:定位字符位置
instr(string,"character",sp,showtimes)
string:表达式,字符
character:表达式中的字符,即需要定位的字符
sp:开始位置
showtimes:出现的次数