oracle操作字符串：拼接、截取、查找、替换_oracle拼接字符串函数

相关文章推荐
稳重的葫芦 · oracle操作字符串：拼接、截取、查找、替 ...· 2 天前 ·
玉树临风的野马 · 如何在PL/SQL上显示添加连字符的字符串- ...· 2 天前 ·
温柔的茴香 · 146亿瓶怡宝撑起一个IPO | 投中网· 5 月前 ·
重感情的伤疤 · 信奥赛时间表 - 抖音· 1 年前 ·
有腹肌的板凳 · 区领导带队赴香港招商：抓项目促投资 ...· 1 年前 ·
俊逸的长颈鹿 · 宝书网打不开了，404怎么办……大哭.jpg ...· 1 年前 ·
眼睛小的生姜 · 10万元级智能音乐座舱新风潮 ...· 1 年前 ·
select concat('拼接', '字符串') as str from dual 
注:oracle的concat函数只支持两个参数的方法，即只能拼接两个参数，如要拼接多个参数则嵌套使用concat可实现，如:




    
 
select concat(concat('拼接', '多个'), '字符串') from dual 
2.1、截取字符串
 
格式1:substr(string string, int a, int b); 
1、string 需截取的字符串 
2、a截取的开始位置，（0,1都表示为第一个元素） 
3、b要截取的字符串长度 
select substr(to_char(sysdate,'yyyy-mm-dd HH:mi:ss'), 12, 5) as time from dual; 
--返回：04:17，截取2021-12-27 16:04:17
select substr('HelloWorld',0,3) value from dual; 
--返回结果：Hel，截取从“H”开始3个字符 
select substr('HelloWorld',1,3) value from dual; 
--返回结果：Hel，截取从“H”开始3个字符 
select substr('HelloWorld',2,3) value from dual; 
--返回结果：ell，截取从“e”开始3个字符
格式2:substr(string string, int a); 
1、string 需截取的字符串 
2、a 从第a个元素到最后一个元素. 
select substr('HelloWorld',0) value from dual;  
--返回结果：HelloWorld，截取所有字符
select substr('HelloWorld',1) value from dual;  
--返回结果：HelloWorld，截取所有字符
select substr('HelloWorld',2) value from dual;  
--返回结果：elloWorld，截取从“e”开始之后所有字符
2.2、截取字符串
 
REGEXP_SUBSTR(string, pattern, position, occurrence, modifier) 
1、string:需要进行正则处理的字符串 
2、pattern：进行匹配的正则表达式 
3、position：起始位置，从字符串的第几个字符开始正则表达式匹配（默认为1） 注意：字符串最初的位置是1而不是0 
4、occurrence：获取第几个分割出来的组（分割后最初的字符串会按分割的顺序排列成组） 
5、modifier：模式（‘i’不区分大小写进行检索；‘c’区分大小写进行检索。默认为’c’）针对的是正则表达式里字符大小写的匹配 
select regexp_substr('1|3767|3767|3766|0', '[^|]+', 1, 1) a,
       regexp_substr('1|3767|3767|3766|0', '[^|]+', 1, 2) b,
       regexp_substr('1|3767|3767|3766|0', '[^|]+', 1, 3) c,
       regexp_substr('1|3767|3767|3766|0', '[^|]+', 1, 4) d,
       regexp_substr('1|3767|3767|3766|0', '[^|]+', 1, 5) e
from dual;
--返回结果 a:1 b:3767 c:3767 d:3766 e:0
解析：
 该处使用正则表达式 [^|]+ 
中括号[] 中的内容代表可选字符集
^ 在正则表达式中一般用于匹配输入字符串的开始位置， 但在中括号中代表反选，也就是排除的字符。
+ 代表匹配一次或多次
所以综合起来， 以上正则表达是的意义是： 匹配不包含|的非空字符串。 
如何查询匹配的最后一组
 
 在不知道具体长度时，可以结合length和regexp_replace 函数运算出分隔符的数量或是匹配到的总数。
 思路就是： 将分隔符（这里是,）使用regexp_replace删除掉， 用原字符长长度减去替换后的字符串长度， 就是包含的分割符的数量，分隔符数量加1 ，就是总共匹配的数量了。 
select length('1|3767|3767|3766|0')-length(regexp_replace('1|3767|3767|3766|0','\|','' ))+1 from dual;
--返回结果:5 因为有四个|匹配，所以会分成4+1=5组
--结合上面的方式， 匹配最后一个值的完整SQL如下：
SELECT REGEXP_SUBSTR('1|3767|3767|3766|0','[^|]+',1,
length('1|3767|3767|3766|0')-length(regexp_replace('1|3767|3767|3766|0','\|','' ))+1) AS STR FROM DUAL;
--返回结果:0
如何查询匹配的所有结果，结果集以行输出
 
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=3;
LEVEL
1
2
3 
 思路就是： 将分隔符（这里是,）使用regexp_replace删除掉， 用原字符长长度减去替换后的字符串长度， 就是包含的分割符的数量，分隔符数量加1 ，就是总共匹配的数量了。结合上面的方式，将LEVEL为各个结果的occurrence（分得第几组）循环输出。 
SELECT REGEXP_SUBSTR('AA,BB,CC','[^,]+',1,LEVEL) AS STR FROM DUAL
CONNECT BY LEVEL <= LENGTH('AA,BB,CC') - LENGTH(REGEXP_REPLACE('AA,BB,CC', ',',''))+1;
STR
AA
BB
CC 
3、查找字符串
 
instr()函数 
格式一：instr(string,subString) 
格式二：instr(string,subString,position,ocurrence) 
1、string：源字符串 
2、subString：要查找的子字符串 
3、position：查找的开始位置 
4、ocurrence：源字符串中第几次出现的子字符串 
select instr('helloworld','l') from dual; 
--返回结果：3 默认第一次出现“l”的位置
select instr('helloworld','lo') from dual; 
--返回结果：4 即“lo”同时出现，第一个字母“l”出现的位置
select instr('helloworld','wo') from dual; 
--返回结果：6 即“wo”同时出现，第一个字母“w”出现的位置
 注：MySQL中的模糊查询 like 和 Oracle中的 instr() 函数有同样的查询效果； 如下所示： 
MySQL： select * from tableName where name like '%helloworld%';
Oracle：select * from tableName where instr(name,'helloworld')>0;  
--这两条语句的效果是一样的
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”的位置
select instr('CORPORATE FLOOR','OR', 3, 2) as loc from dual; --返回结果：14    
4、替换字符串
 
replace(strSource, str1, str2) 将strSource中的str1替换成str2 
1、strSource:源字符串 
2、str1: 要替换的字符串 
3、str2: 替换后的字符串 
select '替换字符串' as oldStr, replace('替换字符串', '替换', '修改') as newStr from dual 
--返回结果：oldStr 替换字符串 newStr 修改字符串
				具体场景：在动态SQL中，我们需要把一段字符加工为我们所需要的SQL，这里举个例子，当前台传入字符为 ‘a,b,c,d’ ，需要把该字符加工为 (‘a’,‘b’,‘c’,‘d’) ，作为in语句的条件才能使用。知道了怎么拆分和拼接，就有了思路，其他场景的分割和拼接也就好做了。
拆分并加工拼接SQL：
--输入 'a,b,c,d'
--输出 ('a','b','c','d')
FUNCTION splitAndHandleStr(
   piStr    IN  varchar2,  --需要加工的字符串.
  b.province || '-' || b.city || '-' || b.Address as location_descr
  elearning.Opt_User a 
    elearning.Opt_Training b ON a.Trainingid = b.Id 
    elearning.Core_Userprofile u ON a.Userid = u.Id 
WHERE 
				1. 准备数据
CREATE TABLE GradesTable (Name VARCHAR2(6), Languages VARCHAR2(6),Mathematics VARCHAR2(6), English VARCHAR2(6) );
insert into GradesTable (Name,Languages,Mathematics,English) values('李明','88','67','98');
insert into GradesTable (Name,Languages,Math
SELECT "我的成绩为" || score FROM TABLE1 T1 WHERE T1.NAME="souter"
还可以使用CONCAT()方法进行字符串拼接，注意，CONCAT()方法只支持两个参数
SELECT CONCAT('成绩为',score) FROM TABLE1 T1 WHERE T1.NAME='sout...
- `string`: 需要截取的原始字符串。
- `start_position`: 起始位置，表示从哪个字符开始截取，默认值是1（如果省略则默认从第一个字符开始）。
- `length`: 截取的字符数，如果提供，则只返回指定长度的字符；如果不提供，那么会一直截取到字符串结束。
例如，如果你想从字符串`'Hello, World!'`中截取前5个字符，你可以这样做：
```sql
SELECT SUBSTR('Hello, World!', 1, 5) FROM dual;
这将返回`'Hello'`。
另外，如果你想要拼接字符串，Oracle提供了`CONCAT()`或`||`操作符，如：
```sql
SELECT CONCAT('Hello', ' ', 'World') FROM dual; 或者 SELECT 'Hello' || ' World' FROM dual;
这两条语句都会得到`'Hello World'`的结果。
2.1、截取字符串

2.2、截取字符串

`如何查询匹配的最后一组`

`如何查询匹配的所有结果，结果集以行输出`

3、查找字符串

4、替换字符串
