文章持续更新来了，哈哈哈！
我们在查询数据的时候，通常会遇到一些字符串的处理，这一章我们来进行一下有关于这方面的一些内置函数的总结。
1、走查字符串（遍历字符串）
还记得我们之前在数据库语法总结（3）——多表联查中提到的笛卡尔积的内容吗？我们现在将运用笛卡尔积的用法，不过与上次提到的不同，我们不是避免，而是运用。
结合数据库语法总结（2）——排序用法中第3点内容，把T_EMP中EMPNAME='cai'，分割开来
SELECT substr(e.EMPNAME,t.pos,1) as C
FROM (SELECT EMPNAME FROM T_EMP WHERE EMPNAME='cai') e,
(select rownum AS pos from USER_TABLES ) t
WHERE t.pos<=length(e.EMPNAME);
解释一下：e中的where条件只是用来找到对应被分开的字符串，也可以写成EMPID=1；
t表可以是任意一张表，只要满足数量比‘cai’的长度3多就可以，因为t.pos<=length(e.EMPNAME)会限制最终使用数量。
也可以根据自身需求改变语句
SELECT substr(e.EMPNAME,t.pos) as A,
substr(e.EMPNAME,length(e.EMPNAME)-t.pos+1) as B,
substr(e.EMPNAME,t.pos,1) as C
FROM (SELECT EMPNAME FROM T_EMP WHERE EMPNAME='cai') e,
(select rownum AS pos from USER_TABLES ) t
WHERE t.pos<=length(e.EMPNAME);
2、在查询出的字符串中加入引号显示
此时我们注意，引号都必须是成对出现的，而要查询字符串则必须要用单引号查询，所以想要查询一个单引号，则是如下：
SELECT '''' AS c FROM dual; 
加入字符结合看一下：
SELECT 'Hello''wrold' AS c FROM dual;
如果只是两个单引号，则查询结果为NULL
SELECT '' AS c FROM dual; 
3、计算字符串中特殊字符出现次数
例如计算 HELLO,HEOOL, 中, 出现次数，基本思路是先用替换函数replace()将,替换成空，然后用之前字符串长度减去替换之后的长度,最后除以特殊字符本身的长度
SELECT LENGTH('HELLO,HELLO,')-
LENGTH(REPLACE('HELLO,HELLO,',',',''))/LENGTH(',') AS cnt
FROM dual;注意，/length(',')是一定要有的，如果特殊字符长度本身就是1位，不会影响结果，但是位数不是1，不加此句将会影响结果，为了大家理解，我们查找LL出现的次数
SELECT (LENGTH('HELLO,HELLO,')-
LENGTH(REPLACE('HELLO,HELLO,','LL','')))/LENGTH('LL') AS cnt ,
(LENGTH('HELLO,HELLO,')-
LENGTH(REPLACE('HELLO,HELLO,','LL',''))) AS cnt_ERROR
FROM dual;
4、删除不想要的字符；将字符和数字分开
例如删除类似金额数据中的,￥$等等，将d4分开成为两列。其实我们之前的章节已经大致讲过了，请参考数据库语法总结（2）——排序用法第4点内容。此处有部分补充Mysql虽然没有translate函数，但支持replace函数，可以尝试多次使用replace进行转换。以Oracle为例：
--现在做三件事：
--1、将DENAME中元音(a,e,i,o,u)字母删除 变成DENAME_CHANGE
--2、将DENO中带有0的数字删除 变成DENO_CHANGE
--3、将DENAME中数字和字母分开 变成DENAME_NUM和DENAME_CHAR
SELECT d.ID ,d.DENAME ,d.DENO,
replace(TRANSLATE(d.DENAME,'aeiou','#####'),'#','') DENAME_CHANGE,
replace(TRANSLATE(d.DENO,'0','#'),'#','') DENO_CHANGE,
replace(TRANSLATE(d.DENAME,'0123456789','0000000000'),'0') DENAME_CHAR,
to_number(
replace(
TRANSLATE(lower(d.DENAME),'abcdefghijkmlnopqrstuvwxyz',rpad('#',26,'#'))
,'#',''),
'9999') DENAME_NUM
FROM T_DEMP d ORDER BY d.ID;
扩展：lower()将字符串变成小写；同样upper()将字符串全部变成大写；initcap()将字符串首字母大写，其余字符小写。
rpad（起始字符,字符长度，填充字符）：函数从右边对字符串使用指定的字符进行填充，例如：rpad('#',5,'$')结果就是#$$$$
如果省略第三个参数则用空格代替，例如：rpad('abcdefghi',5)结果就是abcde；rpad('abc',5)结果就是‘abc ’。
同样填充函数在各个数据库中的关键字不同：DB2：repeat('#',5)；Sqlserver：replicate('#',5)都只需要2个参数。
5、保留字符串中只含有数字和字母的字段
我们有T_DEMP_2表中DENAME字段的值是yang123aaa666r#$%^&0,但是我们只想查询有字母和数字的值，有两种方法，以Oracle为例：
--通过替换夫比较字符是否相同
SELECT d.DENAME
FROM T_DEMP_2 d WHERE translate(lower(d.DENAME),
'0123456789abcdefghijkmlnopqrstuvwxyz',rpad('a',36,'a')) =
rpad('a',LENGTH(d.DENAME),'a');
--通过正则表达式 （两种都可以）
select d.DENAME from T_DEMP_2 d
WHERE regexp_like(d.DENAME,'[^0-9a-zA-Z]+$');
select d.DENAME from T_DEMP_2 d
WHERE NOT regexp_like(d.DENAME,'[^0-9a-zA-Z]');扩展：Mysql中正则的关键字是 regexp '[^0-9a-zA-Z]'=0；1为假，0为真
而我们如果想要除去特殊字符，只保留数字字母可以多次替换
SELECT d.DENAME,
translate(lower(d.DENAME),'0123456789abcdefghijkmlnopqrstuvwxyz',rpad('a',36,'a')) AS aaa,
REPLACE(d.DENAME,
REPLACE(
translate(lower(d.DENAME),'0123456789abcdefghijkmlnopqrstuvwxyz',rpad('a',36,'a'))
,'a','')
,'') AS bbb
FROM T_DEMP_2 d; 
6、提取姓名中的首字母
有一个这样的字符串'Li Ming'我们想要得到'L.M.'的结果
其实还是上面提到的替换函数加拼接解决。除了Mysql数据库较为特殊，其余的都只是关键字的不同，逻辑大致相通，此处就以Oracle和Mysql为例
Oracle
select replace(
replace(
translate(replace('Li Ming','.',''),
'abcdefghijkmlnopqrstuvwxyz',
rpad('#',26,'#')),
'#',''),
' ','.') || '.'
from dual;Mysql(手打的，可能会有错误，大家能理解大概意思就可以)
select case when cnt=2 then
trim(trailing '.' from
concat_ws('.',
substr(substring_index(name,' ',1),1,1),
substr(name,
length(substring_index(name,' ',1))+2,1),
substr(substring_index(name,' ',-1),1,1),'.'))
else
trim(trailing '.' from
concat_ws('.',
substr(substring_index(name,' ',1),1,1),
substr(substring_index(name,' ',-1),1,1) ))
end as initials
from (
select name,length(name)-length(replace(name,' ','')) as cnt
from(
select replace('Li Ming','.','') as name from T1
)y
)x;扩展：Mysql中没有类似于Oracle中的虚拟表dual，所有查询需要有实体表；
concat()函数用于拼接之前说过，concat_ws()函数是特殊的拼接函数，大致组成是concat_ws(分割符,参数字段1,参数字段2……)，可以利用分隔符来拼接字符串
substr()截取字符串之前的章节也提到过，substring_index()的用法有些区别，大致组成是
SUBSTRING_INDEX(str, delimiter, number)
返回从字符串str的第number个出现的分隔符delimiter之前的子串;如果number是正数，那么就是从左往右数，返回第number个分隔符的左边的全部内容;相反，如果number是负数，那么就是从右边开始数，第number个分隔符右边的所有内容
注意:如果number超过了实际分隔符的个数，则返回实际个数的字符串(原文链接可以参考详读)
原文链接：https://blog.csdn.net/Hudas/article/details/123497927
7、表中的行分组转列
我们现在有T_DEMP_3的表，结构是这样的，但是我想变成后面的分组转列的模式
DB2使用listagg(字段,',') within group (order by )函数
SELECT d.ID ,listagg(d.DENAME,',') WITHIN group(ORDER BY d.ID) AS DENAME
FROM T_DEMP_3 d
GROUP BY d.ID;Oracle可以使用listagg(字段,',') within group(order by )函数，也可以使用内置函数sys_connect_by_path()
--使用listagg()
SELECT d.ID ,listagg(d.DENAME,',') WITHIN group(ORDER BY d.ID) AS DENAME
FROM T_DEMP_3 d
GROUP BY d.ID;
--使用SYS_CONNECT_BY_PATH()
SELECT d.ID ,
ltrim(SYS_CONNECT_BY_PATH(d.DENAME,','),',') AS DENAME
FROM (
SELECT ID,DENAME,ROW_number() OVER (PARTITION BY ID ORDER BY DENAME) rn,
count(*) OVER (PARTITION BY ID) cnt
FROM T_DEMP_3
) d
WHERE LEVEL =cnt
START WITH rn=1
CONNECT BY PRIOR ID =ID AND PRIOR rn=rn-1;扩展：ltrim(c1,[,c2])理解为ltrim(执行字符,删除字符)，用于删除左边出现的字符串。按照c2中的字符一个一个的截断c1的字符，而且还是从左开始执行的，一旦遇到c2中的字符，c1中的字符都会相对应的截断，一直到c1的字符没有c2的字符为止才会结束。
相反对应函数rtrim()从右边开始删除
相似函数：lpad() 在列的左边粘贴字符；rpad() 在列的右边粘贴字符
注意：sys_connect_by_path()函数会在列的开头就先添加指定的分隔符，该函数是使用二叉树结构（或叫目录式结构）进行查询与整合的，需要与connect by结合使用。
connect by 语句是将表内数据通过递归方式以树的形式整合与展示，其中start with的作用是指定数的根节点。
而connect by需要与point结合使用，例如：connect by prior child_col = parent_col
其中prior指定根节点的 child_col 作为生长字段，寻找其他数据行中 parent_col字段与其值相同的节点 作为根节点的子节点。
Mysql使用内置函数group_concat()
select d.ID,
group_concat(d.DENAME order by d.ID separator ',') as DENAME
from T_DEMP_3 d
group by d.ID;注意：group_concat()是聚合函数需要加group by 使用
PostgreSQL、Sqlserver使用string_agg()
select d.ID,
string_agg(d.DENAME order by d.ID separator,',') as DENAME
from T_DEMP_3 d
group by d.ID;注意：string_agg()用法和group_concat()类似，都是聚合函数需要加group by 使用
8、将字符串分开转换成in的条件值
例如：现在有字符串'1,2,3'，按照上面提到的表T_DEMP_3字段ID查询，
SELECT d.ID ,d.DENAME,d.DENO FROM T_DEMP_3 d WHERE d.ID IN ('1,2,3');
这样会报错，因为ID是数值类型，那我们要如何转换呢
DB2使用locate()和substr()
select ID,DENAME
from T_DEMP_3 where ID in (
select cast(substr(c,2,locate(',',c,2)-2) as integer) ID
from (
select substr(csv.emps,cast(iter.pos as integer)) as c
from
(select ','||'1,2,3'||',' emps
from t1)csv,
(select id as pos from t100)iter
where iter.pos=length(csv.emps)
)x
where length(c)>1
and substr(c,1,1)=','
)扩展：locate()返回一个字符串在另一个字符串中的位置，语法：locate(搜索的字符串，被搜索的字段,搜索的起始位置)。第二个参数注意长度不能超过255字节。第三个参数如果是负数，则是反方向偏移起始位置，例如-1，就是最后一个字符。
Mysql
select ID,DENAME
from T_DEMP_3
where ID in
(
select substring_index(
substring_index(list.vals,',',iter.pos),',',-1) ID
from (select id from t100) as iter,
(select '1,2,3' as vals from t1)list
where iter.pos<+
(length(list.vals)-length(replace(list.vals,',','')))+1
)Oracle使用substr()、instr()、rownum
select ID,DENAME
from T_DEMP_3
where ID in(
select to_number(
rtrim(
substr(emps,instr(emps,',',1,iter.pos)+1,
instr(emps,',',1,iter.pos+1)-
instr(emps,',',1,iter.pos)),
',')) emps
from
(select ','||'1,2,3'||',' emps FROM dual)csv,
(select rownum pos from T_DEMP_3)iter
where iter.pos<=((length(csv.emps)-
length(replace(csv.emps,',')))/length(','))-1
);扩展：
instr()函数的格式 （俗称：字符查找函数）返回类型为数值类型
格式一：instr( string1, string2 ) / instr(源字符串, 目标字符串)
格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] ) / instr(源字符串, 目标字符串, 起始位置, 匹配序号)
解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2。
PostgreSQL
select ID,DENAME
from T_DEMP_3
where ID in(
selecr cast(ID as integer) as ID
from(
select split_part(list.vals,',',iter.pos) as ID
from(select ID as pos from t100)iter,
(select ','||'1,2,3'||',' as vals from t1)list
where iter.pos<=
length(list.vals)-length(replace(list.vals,',',''))
)z
where length(ID)>0
)扩展：split_part()切割函数。语法：split_part(分割字符串,分隔符,返回第几段分隔出的字符)
有的会有四个参数split_part(分割字符串,分隔符,开始返回字符，结束返回字符)
SQL server
select ID,DENAME
from T_DEMP_3
where ID in
(select substring(c,2,charindex(',',c,2)-2) as ID
from (
select substring(csv.emps,iter.pos,len(csv.emps)) as c
from (select ','+'1,2,3'+',' as emps from t1)csv,
(select id as pos from t100)iter
where iter.pos<=len(csv.emps))x
where len(c)>1 and substring(c,1,1)=','
)扩展：charindex(目标字符串,被查找的字符串,开始查找的位置，为空时默认从第一位开始查找)类似于Oracle查找字符instr()
同时提一下string_split()表值函数，它将字符串拆分为一个表，该表由基于指定分隔符的子字符串行组成。语法如下：
STRING_SPLIT ( input_string , separator )
在这个语法中：
input_string- 是一个基于字符的表达式，其值为NVARCHAR，VARCHAR，NCHAR或CHAR字符串。separator- 是一个单个字符，用作拆分的分隔符。- 注意string_split只能接受单个单元格内容，查询表只能放一个字段
更多请阅读：https://www.yiibai.com/sqlserver/sql-server-string_split-function.html
len() 函数返回输入字符串的字符数长度，不包括尾随空格。
9、字符串按照26位字母排序
还是在T_DEMP_3中有一个DENO字段，我想变成如下NEWNO样子
下面以Oracle为例，思路是将DENO的字段每个字符分别拆分出来，排序后，再用上面第7点提到的组装成列
SELECT DENO,
LISTAGG(c,'') WITHIN GROUP (ORDER BY c) AS newno
FROM (
SELECT a.DENO,substr(a.DENO,iter.pos,1)AS c
FROM T_DEMP_3 a,
(select rownum AS pos from USER_TABLES WHERE rownum<7 ) iter
WHERE iter.pos<=length(a.DENO)
ORDER BY 1,2
) x
GROUP BY DENO ;
10、识别可视为数字的字符串
我们在T_DEMP_3中新增了字段MIXED，字段格式如下，既有数字也有字母，还有组合形式，但是我们现在只想保留数字
其实基本思路还是利用上面的替换函数replace和translate把字母替换之后利用查找函数判断条件
Oracle的范例
SELECT TO_NUMBER(
CASE WHEN REPLACE(
TRANSLATE(mixed,'0123456789','9999999999'),'9') IS NOT NULL
THEN
REPLACE(
TRANSLATE(mixed,
REPLACE(
TRANSLATE(mixed,
'0123456789','9999999999'),'9'),
RPAD('#',LENGTH(mixed),'#')),'#')
ELSE
mixed
END
) mixed,mixed
FROM T_DEMP_3
WHERE instr(TRANSLATE(mixed,'0123456789','9999999999'),'9')>0;扩展：这里的查找函数是instr(),我们上面有介绍如何使用,返回类型是数值类型
DB2:使用posstr(),这里不得不提一下上面同样介绍的locate() 。locate(arg1,arg2,<pos>) 查找arg2中第一次出现arg1的位置，指定pos；posstr(arg1,arg2)查看arg2在arg1中的位置 ；
Mysql:使用ascii(),ascii(str)用于查找字符表达式最左边字符的ASCII代码；常用于与between……and结合使用确定范围。例如：ascii(mixed) between48 and 57
PostgreSQL:strpos(),strpos(string, substring)；例如：strpos('high','ig')，返回2
Sqlserver:使用isnumeric()和通配联合查找，用于检查指定的表达式是否为数字，例如isnumeric('123')=1；isnumeric('abc')=0;
11、with as语句
特此提一下Oracle查询中的with as子句相当于创建一个临时表，将一个语句中的某些中间结果放在临时表空间。将查询中的子查询命名，放到SELECT语句的最前面。语法如下：
with tempname1 ([字段名称]) as (select.....),
tempname2 ([字段名称]) as (select.....)
12、根据发音比较字符串
大概下面讲的函数只能运用在英语的字符串了，刚开始产生是为了美国人口普查，人名拼写问题，举例大家就明白了
SELECT '123' AS c FROM dual where SOUNDEX('baijuyi')= SOUNDEX('vvvv');不返回值
SELECT '123' AS c FROM dual where SOUNDEX('baijuyi')= SOUNDEX('baiyiju')尽管bai juyi与bai yiju顺序不对，但是大致发音正确，还是返回123
13、关于正则表达式查找文本
上面提到过使用正则表达式查找文本，现在进行一些扩展：
(1) regexp_like : 同 like 功能相似（模糊 ‘匹配’）
(2) regexp_instr : 同 instr 功能相似（返回字符所在 ‘下标’）
(3) regexp_substr : 同 substr 功能相似（‘截取’ 字符串）
(4) regexp_replace: 同 replace 功能相似（ ‘替换’ 字符串）
– 使用了 ‘正则表达式’ 替代了老的 ‘百分比 %’ 和 ‘通配符 _’
– 上述 四个 函数，正则用法相似，知晓一个，其它皆可参考
下面有位博主写的很详细大家可以看一下：
原文链接：https://blog.csdn.net/m0_37253968/article/details/120361221
regexp_replace(source, pattern, replace_string, occurrence)
参数说明：
● source: string类型，要替换的原始字符串。
● pattern: string类型常量，要匹配的正则模式，pattern为空串时抛异常。
● replace_string:string，将匹配的pattern替换成的字符串。
● occurrence: bigint类型常量，必须大于等于0，
大于0：表示将第几次匹配替换成replace_string，
等于0：表示替换掉所有的匹配子串。
其它类型或小于0抛异常。
下面这位博主写的regexp_replace()很详细，推荐一下
原文链接：https://blog.csdn.net/JohnnyChu/article/details/111184962
哈哈哈，这篇就更新到这了，大家有补充可以留言，感觉文章越来越冗长了，所以有些部分我就推荐了一些其他博主的文章进行参考。
247
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
