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

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

一.需求

将全名转换为大写首字母缩写，比如下面的名字:
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 (
   select replace('Stewie Griffin Boy','.','') as name
          ) y
          ) x;

测试记录

mysql> -- 2个
mysql> 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;
+----------+
| initials |
+----------+
| S.G      |
+----------+
1 row in set (0.00 sec)

mysql>
mysql> -- 3个
mysql> 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 Boy','.','') as name
    ->           ) y
    ->           ) x;
+----------+
| initials |
+----------+
| S.G.B    |
+----------+
1 row in set (0.00 sec)

看到这一长串是不是很迷糊，我们把sql代码拆开来

-- 如果名称里面存在 '.' 替换为空
mysql> select replace('Stewie Griffin','.','') as name;
+----------------+
| name           |
+----------------+
| Stewie Griffin |
+----------------+
1 row in set (0.00 sec)

-- 判断是  名和姓，或是由名、中间名(中间名缩写)及姓
mysql>  select name,length(name) - length(replace(name,' ','')) as cnt
    ->      from (
    ->    select replace('Stewie Griffin','.','') as name
    ->           ) y;
+----------------+------+
| name           | cnt  |
+----------------+------+
| Stewie Griffin |    1 |
+----------------+------+
1 row in set (0.00 sec)

mysql> -- 通过substring截取' '第一次出现的地方，保留左边的
mysql> select substring_index('Stewie Griffin',' ',1) as n1;
+--------+
| n1     |
+--------+
| Stewie |
+--------+
1 row in set (0.00 sec)

mysql>
mysql> -- 截取第一个大写的字母
mysql> select substr('Stewie',1,1) as n2;
+------+
| n2   |
+------+
| S    |
+------+
1 row in set (0.00 sec)

mysql>
mysql> -- 联合起来就是这样的
mysql> select substr(substring_index('Stewie Griffin',' ',1),1,1) as n1;
+------+
| n1   |
+------+
| S    |
+------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql>
mysql> -- 通过substring ' '最后一次出现的地方，保留右边的
mysql> select substring_index('Stewie Griffin',' ',-1) as n1;
+---------+
| n1      |
+---------+
| Griffin |
+---------+
1 row in set (0.00 sec)

mysql>
mysql> -- 截取第一个大写的字母
mysql> select substr('Griffin',1,1) as n2;
+------+
| n2   |
+------+
| G    |
+------+
1 row in set (0.00 sec)

mysql>
mysql> -- 联合起来就是这样的
mysql> select substr(substring_index('Stewie Griffin',' ',-1),1,1) as n1;
+------+
| n1   |
+------+
| G    |
+------+
1 row in set (0.00 sec)

mysql>
mysql>
-- trim 去掉最后面的'.'如存在
mysql> select trim(trailing '.' from 'S.G') as str;
+------+
| str  |
+------+
| S.G  |
+------+
1 row in set (0.00 sec)

mysql> select trim(trailing '.' from 'S.G.') as str;
+------+
| str  |
+------+
| S.G  |
+------+
1 row in set (0.00 sec)

mysql> select trim(trailing '.' from '.S.G.') as str;
+------+
| str  |
+------+
| .S.G |
+------+
1 row in set (0.00 sec)

三.使用MySQL函数

代码:

-- 用函数进行封装
CREATE FUNCTION `f_get_name`(`pi_str` varchar(200)) RETURNS varchar(200) CHARSET utf8mb4
    DETERMINISTIC
BEGIN
	#Routine body goes here...
  
  declare o_name varchar(200) default null;
  declare l_cnt int default null;
  declare o_name1 varchar(200) default null;
  declare o_name2 varchar(200) default null;
  declare o_name3 varchar(200) default null;
  

  SELECT length(pi_str) - length(replace(pi_str,' ',''))
  into l_cnt;
  
  if l_cnt = 1 then
  SELECT substr(substring_index(pi_str,' ',1),1,1)
  into o_name1;

  SELECT substr(substring_index(pi_str,' ',-1),1,1)
  into o_name2;

  SELECT concat(o_name1,'.',o_name2)
  into o_name;
	
  elseif l_cnt = 2 THEN
   
  SELECT substr(substring_index(pi_str,' ',1),1,1)
  into o_name1;
  
  SELECT substr(pi_str,length(substring_index(pi_str,' ',1)) + 2,1)
  into o_name2;

  SELECT substr(substring_index(pi_str,' ',-1),1,1)
  into o_name3;

  SELECT concat(o_name1,'.',o_name2,'.',o_name3)
  into o_name;
   
  
  else
    BEGIN
    end;
  end if;

	RETURN o_name;
END

函数封装后，SQL代码真的简单太多

mysql> select f_get_name('Stewie Griffin') as name;
+------+
| name |
+------+
| S.G  |
+------+
1 row in set, 1 warning (0.00 sec)

mysql> select f_get_name('Stewie Griffin Boy') as name;
+-------+
| name  |
+-------+
| S.G.B |
+-------+
1 row in set (0.00 sec)