1、nstr()函数的格式 (俗称:字符查找函数)
格式一:
instr( string1, string2 ) -- instr(源字符串, 目标字符串)
格式二:
instr( string1, string2, start_position , nth_appearance ) --- instr(源字符串, 目标字符串, 起始位置, 匹配序号)
解析:string2 的值要在string1中查找,是从start_position给出的数值(即:位置)开始在string1检索,检索第nth_appearance(几)次出现string2。
注:在Oracle/PLSQL中,instr函数返回要截取的字符串在源字符串中的位置。只检索一次,也就是说从字符的开始到字符的结尾就结束。
2、实例
格式一
select instr('helloworld','l') from dual;
--返回结果:3 默认第一次出现“l”的位置
select instr('helloworld','lo') from dual;
--返回结果:4 即“lo”同时(连续)出现,“l”的位置
select instr('helloworld','wo') from dual;
--返回结果:6 即“w”开始出现的位置
格式二
select instr('helloworld','l',2,2) from dual;
--返回结果:4 也就是说:在"helloworld"的第2(e)号位置开始,查找第二次出现的“l”的位置
select instr('helloworld','l',3,2) from dual;
--返回结果:4 也就是说:在"helloworld"的第3(l)号位置开始,查找第二次出现的“l”的位置
select instr('helloworld','l',4,2) from dual;
--返回结果:9 也就是说:在"helloworld"的第4(l)号位置开始,查找第二次出现的“l”的位置
select instr('helloworld','l',-1,1) from dual;
--返回结果:9 也就是说:在"helloworld"的倒数第1(d)号位置开始,往回查找第一次出现的“l”的位置
select instr('helloworld','l',-2,2) from dual;
--返回结果:4 也就是说:在"helloworld"的倒数第2(l)号位置开始,往回查找第二次出现的“l”的位置
select instr('helloworld','l',2,3) from dual;
--返回结果:9 也就是说:在"helloworld"的第2(e)号位置开始,查找第三次出现的“l”的位置
select instr('helloworld','l',-2,3) from dual;
--返回结果:3 也就是说:在"helloworld"的倒数第2(l)号位置开始,往回查找第三次出现的“l”的位置
注:MySQL中的模糊查询 like 和 Oracle中的 instr() 函数有同样的查询效果; 如下所示:
MySQL: select * from tableName where name like '%helloworld%';
Oracle:select * from tableName where instr(name,'helloworld')>0;
--这两条语句的效果是一样的
3、实现截取指定字符串
原始输入如下
截取括号中间的数据
SELECT substr(director,
instr(director, '(') + 1,
instr(director, ')') - instr(director, '(') - 1
)
FROM AAC_IT00026_SECURITY
执行结果如下
截取括号前的数据
SELECT substr(director, 0, instr(director, '(') - 1)
FROM AAC_IT00026_SECURITY
4、NVL(Expr1,Expr2)返回不为null的表达式值
NVL(Expr1,Expr2)如果Expr1为NULL,返回Expr2的值,否则返回Expr1的值。
NVL2(Expr1,Expr2,Expr3)如果Expr1为NULL,返回Expr2的值,否则返回Expr3的值。
NULLIF(Expr1,Expr2)如果Expr1和Expr2的值相等,返回NULL,否则返回Expr1的值。
比如:
SELECT extractvalue(document_data,'/root/orderNum') as orderNum,
extractvalue(document_data,'/root/text_IT00022_ComputerList_display') as ComputerList,
--NVL(extractvalue(document_data,'/root/text_IT00022_ComputerList_display'),extractvalue(document_data,'/root/IT00022_ComputerList')) as ComputerList,
extractvalue(document_data,'/root/OriginalAuth') as OriginalAuth,
extractvalue(document_data,'/root/GrantAuth') as GrantAuth,
extractvalue(document_data,'/root/ValidateTimeStart') as ValidateTimeStart,
extractvalue(document_data,'/root/StartDate') as StartDate,
extractvalue(document_data,'/root/EndDate') as EndDate,
extractvalue(document_data,'/root/empName') as empName,
extractvalue(document_data,'/root/email') as email,
extractvalue(document_data,'/root/empCompany') as empCompany,
extractvalue(document_data,'/root/empDept') as empDept,
extractvalue(document_data,'/root/text_IT00012_district_display') as district,
extractvalue(document_data,'/root/text_FacilityType_display') as FacilityType,
extractvalue(document_data,'/root/ReasonFacility') as ReasonFacility,
extractvalue(document_data,'/root/ReasonDescription') as ReasonDescription,'auto' as openPerson,'无' as resultAuto
FROM (select document_data from DAT_DOCUMENT D where FORM_NAME='IT00022' and ( DOCUMENT_STATUS is null or DOCUMENT_STATUS !='deleted' ) )
where extractvalue(document_data,'/root/orderNum') = 'IT0002220200730002'
由于不存在text_IT00012_district_display,则text_IT00012_district_display查询的为null。所以返回IT00022_ComputerList对应字段的值。