Oracle 命令 之 substr （截取） instr（查找） replace(替换)

 

（1）oracle中实现截取字符串：substr

substr(string, start_position, [length])

其中，string是元字符串，start_position为开始位置，length是可选项，表示子字符串的位数。

例子：

substr('ABCDEFG', 0);    -- 返回结果是：ABCDEFG，从0位开始截取后面所有

substr('ABCDEFG', 2);    -- 返回结果是：BCDEFG，从2位开始截取后面所有

substr('ABCDEFG', 0, 3);  -- 返回结果是：ABC，从0位开始往后截取3个字符长度

substr('ABCDEFG', 0, 100);  -- 返回结果是：ABCDEFG，虽然100超出了元字符串长度，但是系统会按元字符串最大数量返回，不会影响返回结果

substr('ABCDEFG', -3);  -- 返回结果是：EFG，如果是负数，则从尾部往前数，截取-3位置往后的所有字符串

（2）查找字符串位置：instr

instr(string, subString, [start_position, [nth_appearance]])

其中，string是元字符串；subString是要查找的子字符串；start_position是要查找的开始位置，为可选项（默认为1），注意在这里字符串索引从1开始，如果此参数为正，则从左到右检索，如果此参数为负，则从右到左检索；nth_appearance是元字符串中第几次出现的子字符串，此参数可选，缺省默认为1，如果是负数则系统报错。

例子：

instr('ABCDABCDAEF', 'AB');   -- 返回结果是：1，因为instr字符串索引从1开始，所以是1不是0

instr('ABCDABCDAEF', 'DA', 1, 2);   -- 返回结果是：8，返回第二次出现'DA'的位置

instr('A BCDABCDAEF', 'DA', 1, 2)；  -- 返回结果是：9，由于我在元字符串中加了一个空格，空格仍然算一个字符

（3）替换字符串：replace

replace(str1, str2, str3)

其表示的意思是：在str1中查找str2，凡是出现str2的地方，都替换成str3。

replace('ABCDEFG', 'CDE', 'cde');  -- 返回结果是：ABcdeFG

replace('ABCDEFG', 'CDE', '');   -- 返回结果是：ABFG，CDE被替换成空字符

replace('ABCDEFG', 'CDE');   -- 返回结果是：ABFG，当不存在第三个参数时，CDE直接被删掉

 

例如：

要求：A.数据库表中的一个字符串 可能含有"+" 例：ORC+001
　　也可能不含“+”
　　B.要求如果该字符串含有“+”，则取“+”之前的字符 例：ORC+001 取ORC
　　C.如果该字符串不含"+"，则直接取该字符串。
　　解答：利用ORACLE中 INSTR，SUBSTR以及CASE WHEN语法即可实现。
　　Oracle中函数
　　INSTR：INSTR方法的格式为
　　INSTR(源字符串, 目标字符串, 起始位置, 匹配序号)
　　默认查找顺序为从左到右。当起始位置为负数的时候，从右边开始查找。
　　例：Select INSTR(‘ORC+001‘,‘+‘,1,1) from dual
　　返回的是＂４＂　如果该字符串没有匹配字符　返回的是“０”。
　　SUBSTR：取子字符串　取子字符串
　　SUBSTR(源字符串,起始位置,要取多少位)
　　从“起始位置”开始,取“多少＂个
　　例　Select SUBSTR(‘ORC+001‘,1,3) from dual;
　　返回的是“ORC”
　　全例如下
　　表T_RCV_ISSUE
　　WH_CD字段
　　取 WH_CD 如果该字段包含"+"，取“+”之前的字符
　　如果该字段没有"+"，取整个字段
　　T_RCV_ISSUE表
　　WH_CD 分别为 MR-PS+007，MR-WS，MR-WS+001，MR-PS
　　执行
　　SELECT CASE
　　WHEN INSTR(WH_CD, ‘+‘, 1, 1) > 0
　　THEN SUBSTR(WH_CD, 1, INSTR(WH_CD, ‘+‘, 1, 1) - 1)
　　ELSE WH_CD
　　END AS WH_CD
　　from T_RCV_ISSUE
　　结果：MR-PS，MR-WS,MR-WS,MR-PS ......