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

快来打我* 2022-12-13 04:43 137阅读 0赞

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

### 文章目录 ###

*  一.需求
 *  二.解决方案
 *  三.使用MySQL函数

# 一.需求 #

将全名转换为大写首字母缩写，比如下面的名字:  
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)