mySQL提取姓氏_MySQL 提取姓名大写首字母缩写

备注:测试数据库版本为MySQL 8.0

Table of Contents

一.需求

将全名转换为大写首字母缩写，比如下面的名字:

Stewie Griffin

要求返回如下结果

S.G

二.解决方案

需要注意的就是SQL并不像C或Python语言那样灵活，所以，创建一个通用的解决方案来处理所有格式的姓名对于SQL来说不是一件容易的事情。在这里所介绍的解决方案假定名字都是由名和姓，或是由名、中间名(中间名缩写)及姓组成。

代码:

-- 2个

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 Griffin','.','') as name

) y

) x;

-- 3个

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 (

se