遍历字符串
将表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