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

问题：将全名转换为大写首字母缩写。考虑下面的名字：

Stewie Griffin

要返回如下结果：

S.G.

 

解决方案：

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('Stewie Grifin','.','') as name from t1
)y
)x;

转载于:https://www.cnblogs.com/liang545621/p/7523142.html