mysql 处理字符串 减断,MySQL字符串处理[8题]

原始表如下：

emp

dept

第1题，6.1遍历字符串

例：把emp表中的ename等于KING的字符串拆开来显示为4行

需要最终结果：

select substr(ename, t10.id,1) as string

from emp, t10

where t10.id <= length(ename)

and ename='KING'

注意length()函数，而不是len()

第2题，6.3统计字符出现的次数

例：统计字符串中有多少个逗号 ‘10,CLARK,MANAGER’

需要最终结果：

答：

select round((length('10,CLARK,MANAGER') - length(replace('10,CLARK,MANAGER',',','')))/length(',')) as cn

第3题，6.4删除不想要的字符

例：从下表中，删除所有的数字0和元音字母，并将删除后的值显示在strippde1列和strippde2列中

需要最终结果：

答：

select ename,

replace(

replace(

replace(

replace(

replace(

ename,'A',''),'E',''),'I',''),'O',''),'U','') strippde1,

sal,

replace(sal,'0','') strippde2

from emp

第4题，6.7提取姓名的首字母

例：希望把姓名变成首字母的形式，如：Stewie Griffin，得到S.G

(姓名有两种形式要做考虑：1.First Name+Last Name 2.First Name+Middle Name+Last Name)

需要最终结果：

答：

select case when cnt=2

then concat_ws('.', substring(substring_index(name,' ',1),1,1),

substring(name, length(substring_index(name,' ',1))+2,1),

substring(substring_index(name,' ',-1),1,1))

else concat_ws('.', substring(substring_index(name,' ',1),1,1),

substring(substring_index(name,' ',-1),1,1))

end as initials

from (select name, length(name)-length(replace(name,' ','')) as cnt

from (select replace('Stewie Griffin','.','') as name) as y

) as x

第5题，6.10创建分隔列表

例：想把行数据变成以某种符号分隔的列表，例如以逗号分隔，而不是常见的竖排的列数据形式

竖排形式数据：

需要最终结果：

答：

select deptno,

group_concat(ename separator ',') as emps

from emp

group by deptno

#如果要考究一点也可以在group_concat()函数中做点花样

select deptno,

group_concat(ename order by empno separator ',') as emps

from emp

group by deptno

第6题，6.11分隔数据转换为多值in列表

例：如下in列表中只有一个字符串，希望字符换能被当作以逗号分隔的数值列表

select * from emp

where empno in ('7654,7698,7782,7788')

需要最终结果：

答：

select empno, ename, sal, deptno

from emp

where empno in (select substring_index(substring_index(list.vals,',',t10.id),',',-1) as empno

from t10, (select '7654,7698,7782,7788' as vals) list

where t10.id <= (length(list.vals)-length(replace(list.vals,',','')))+1

)

第7题，6.12按字母表顺序排列字符

例：按照字母表顺序，对姓名ename进行单个字母排序

需要最终结果：

答：

select ename, group_concat(c order by c separator '') new_name

from (select ename, substr(ename, t10.id,1) c

from emp, t10

where t10.id <= length(ename)

) x

group by ename

第8题，6.14提取第n个分隔子字符串

例：希望提取下列每一行中，第二个名字

需要最终结果：

答：

#对应的视图，做出了原始表

create view v as

select 'mo,larry,curly' as name

union all

select 'tina,gina,jaun,regina,leena'

#方法1：提取第二个名字

select substring_index(substring_index(v.name,',',2),',',-1) name

from v

#方法2：比较通用id可根据需求变动

select name

from (select t10.id, substring_index(substring_index(v.name,',',t10.id),',',-1) name

from v, t10

where t10.id <= length(v.name)-length(replace(v.name,',',''))+1

) x

where id=2

(易错：最后一句where id=2，不能写成where t10.id=2，否则会报错Error Code: 1054)