SQL字符串处理

遍历字符串

将表emp中ename值为king的字符串显示为4行，每行包含king的一个字符

用笛卡尔积生成行号，用来在该行中返回字符串中的每个字符 select substr(e.ename,iter.pos,1) as c##SUBSTR(str,pos,len); from (select ename from emp where ename = 'king') e, (select id as pos from t10) iter where iter.pos <=length(e.ename)

字符串文字中包含引号

在使用引号时，可以将他们当做括号对待，如果有一个前括号，也必须有一个响应的后括号。在任何字符串中，必须保持引号个数为偶数。

计算字符出现次数

最后一步除法操作是必要的

select (length('10,clark,manager')- length(replace('10,clerk,manager,',','')))/length(',') as cnt from t1

从字符串中删除不必要的字符

多次调用replace

select ename, replace ( replace( replace( replace( replace(ename,'A',''), 'E',''), 'I',''), 'O','')， 'U','') as stripped1,sal,replace(sal,0,'') as stripped2 from emp

将字符和数字数据分离

使用translate函数简单方便（MySQL不支持 ,可用replace代替）

select data, replace(translate(data,'10123456789','0000000000'),'0') ename from (selct ename || sal data from emp)

判别字符串是不是字母数字型的

create view v as select ename as data from emp where deptno = 10 union all select concat(ename , ',$' , sal , '.00' ) as data from emp where deptno = 20 uniona all select concat(ename,deptno) as data from emp where deptno = 30 select data from V where data regexp '[^0-9a-zA-Z]' = 0

提取姓名的大写首字母缩写

select substr(substring_index(name,' ',1),1,1) as a, substr(substring_index(name,' ',1),1,1) as b from (select 'Swewie Griffin' as name from t1 ) x

按字符串中的数值排序

MySQL不支持transcate函数

待完善

根据表中的行创建一个分隔列表

group_concat()函数需要与group by语句在一起使用，才能得到需要的效果

select deptno, group_concat(ename order by empno seprator, ',') as emps from emp order by deptno

将分隔数据转换为多值in列表

select empno, ename, sal, deptno from emp where empno in ( select substring_index(substring_index(list.vals, ',' ,iter.pos), ',' , -1) empno from (select id pos from t10) as iter, (select '7645,7698,7782,7788' as vals) from t1) list where iter.pos <=(length(list.vals)-length(replace(list.vals,',','')))+1 )x

按字母顺序排列字符串

select ename ,group_concat(c order by c seprator '') from ( select ename,substr(a.ename,iter.pos,1) c from emp a, (select id pos from t10 ) iter where iter.pos <= length (a.ename) ) x group by ename

分解IP地址

select substring_index(substring_index(y.ip,'.',1),'.',-1) a, substring_index(substring_index(y.ip,'.',2),'.',-1) b, substring_index(substring_index(y.ip,'.',3),'.',-1) c, substring_index(substring_index(y.ip,'.',4),'.',-1) d, ) from (select '92.111.0.2' as ip from t1) y