《SQL经典实例》：3. string相关函数

目录

replace

concat & concat_ws

substr & substring_index

综合应用(提取姓名首字母)

---

replace

和python中replace用法都差不多。

-- 1. 用于删除不想要的字符
SELECT ename, 
replace(replace(replace(replace(replace(ename,'A',''),'E',''),'I',''),'O',''),'U','') AS stripped1
 FROM emp;
--删除ename中的A,E,I,O,U元音字母


-- 2. 用于统计字符出现次数
SELECT (length('10,CLARK,MANAGER')-length(replace('10,CLARK,MANAGER',',','')))/length(',') AS cnt
 FROM T1;
-- 统计逗号出现次数

concat & concat_ws

• concat: 将两个或多个字符串连接在一起；
• concat_ws: 用某个符号将几个字符串连接在一起。
• group_concat: 将同一分组下检索到的多行记录连接在一起。

-- 1. concat
SELECT concat(ename, ' works as a ', job) as msg
FROM emp
WHERE deptno=10;


-- 2. concat_ws
SELECT concat_ws("-", "SQL", "Tutorial", "is", "fun!") AS ConcatenatedString;


-- 3. group_concat
SELECT deptno, group_concat(ename order by empno separator ',') AS emps
FROM emp
GROUP BY deptno;

group_concat得到结果如下：

substr & substring_index

• substr(str1, 5, 2): 从第五个字符开始，截取str1两个字符；若不声明最后一个参数，则截取到最后
• substring_index(str1, '.', 1): 截取str1第一个'.'出现前的字符串 (substring_index常用于有规律性的特殊符号的字符串中)

这两个截取字符串的函数，常可以和数据透视表搭配使用，通过两个表的笛卡尔积创造循环的效果，来遍历字符串中的单词或字母等。（由于sql中没有循环语句，所以只能用这种方式模拟循环了）

-- 1. substr遍历一个单词的每个字母
SELECT SUBSTR(e.ename, iter.pos) AS A,
	   SUBSTR(e.ename, length(e.ename)-iter.pos+1) AS B
FROM (SELECT ename FROM emp WHERE ename = 'ALLEN') e,
(SELECT ID AS pos FROM T10) iter
WHERE iter.pos <= length(e.ename);



-- 2. substring_index解析ip地址
SELECT substring_index(substring_index(x.ip, '.', 1), '.',-1) a,
	   substring_index(substring_index(x.ip, '.', 2), '.',-1) b,
       substring_index(substring_index(x.ip, '.', 3), '.',-1) c,
       substring_index(substring_index(x.ip, '.', 4), '.',-1) d 
FROM (SELECT '92.03.8.4' AS ip FROM T1) x;



-- 3. substring_index遍历用逗号分隔的一串字符的每个部分
SELECT empno, ename, sal, deptno
FROM emp
WHERE empno IN(
SELECT substring_index(substring_index(list.vals, ',', iter.pos), ',', -1) empno
FROM (SELECT id AS pos FROM T10) iter, 
     (SELECT '7654,7698,7782,7788' AS vals FROM T10) list
WHERE iter.pos <= (length(list.vals)-length(replace(list.vals, ',','')))+1
); 
-- 这里是想得到empno为7654/7698/7782/7788的emp表中的记录，就需要将这个整个的字符串分开

T10表：           1结果：                              2结果：             3结果：

               

基本模式：将数据透视表T10和某个table进行笛卡尔积，从中select出想要的字符串片段，别忘了在后面加个where条件只取需要的循环次数iter.pos(否则substr中的iter.pos参数就超范围了会出错)。

综合应用(提取姓名首字母)

这个例子还不涉及循环，且只考虑2或3个单词作为名字的这两种情况：

SELECT case 
	   WHEN cnt = 2 THEN 
           concat(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 
		   concat(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('Robert Downey Jr', '.','') AS name FROM T1)y)x;