SQL 提取姓名的首字母|极客教程

SQL 提取姓名的首字母，你想把姓名变成首字母的形式，考虑人名 Stewie Griffin，你希望得到 S.G.。

SQL 提取姓名的首字母问题描述

你想把姓名变成首字母的形式，考虑人名 Stewie Griffin，你希望得到 S.G.。

SQL 提取姓名的首字母解决方案

注意，SQL 的灵活性比不上 C 语言或 Python 这样的编程语言。因此，很难使用 SQL 创建一个处理姓名格式转换的通用解决方案。下面给出的解决方案仅适用于两种格式：要么是 First Name 和 Last Name 的组合，要么是 First Name、Middle Name（全称或者首字母均可）和 Last Name 的组合。
DB2
使用内置函数 REPLACE、TRANSLATE 和 REPEAT 提取首字母。

1 select replace(
2        replace(
3        translate(replace('Stewie Griffin', '.', ''),
4                  repeat('#',26),
5                  'abcdefghijklmnopqrstuvwxyz'),
6                   '#','' ), ' ','.' )
7                  ||'.'
8   from t1

MySQL
使用内置函数 CONCAT、CONCAT_WS、SUBSTRING 和 SUBSTRING_INDEX 提取首字母。

 1  select case
 2           when cnt = 2 then
 3             trim(trailing '.' from
 4                  concat_ws('.',
 5                   substr(substring_index(name,' ',1),1,1),
 6                   substr(name,
 7                          length(substring_index(name,' ',1))+2,1),
 8                   substr(substring_index(name,' ',-1),1,1),
 9                   '.'))
10           else
11             trim(trailing '.' from
12                  concat_ws('.',
13                   substr(substring_index(name,' ',1),1,1),
14                   substr(substring_index(name,' ',-1),1,1)
15                   ))
16           end as initials
17    from (
18  select name,length(name)-length(replace(name,' ','')) as cnt
19    from (
20  select replace('Stewie Griffin','.','') as name from t1
21          )y
22          )x

Oracle 和 PostgreSQL
使用内置函数 REPLACE、TRANSLATE 和 RPAD 提取首字母。

1 select replace(
2        replace(
3        translate(replace('Stewie Griffin', '.', ''),
4                  'abcdefghijklmnopqrstuvwxyz',
5                  rpad('#',26,'#') ), '#','' ),' ','.' ) ||'.'
6   from t1

SQL Server
在写作本书时，SQL Server 尚不支持 TRANSLATE 函数和 CONCAT_WS 函数。

SQL 提取姓名的首字母扩展知识

通过分离出大写字母，我们就能从姓名中提取首字母。下面详细解释针对各种数据库的解决方案。
DB2
REPLACE 函数会删除姓名里出现的英文句号（因为有时候 Middle Name 会以首字母形式表示），而 TRANSLATE 函数会把非大写字母都替换为字符 #。

select translate(replace('Stewie Griffin', '.', ''),
                 repeat('#',26),
                 'abcdefghijklmnopqrstuvwxyz')
  from t1
　
TRANSLATE('STE
--------------
S##### G######

此时，除了首字母外，名字的其他部分都变成了 #。然后使用 REPLACE 函数删除所有的 #。

select replace(
       translate(replace('Stewie Griffin', '.', ''),
                  repeat('#',26),
                  'abcdefghijklmnopqrstuvwxyz'),'#','')
  from t1
　
REP
---
S G

再次使用 REPLACE 函数把空格替换为英文句号。

select replace(
       replace(
       translate(replace('Stewie Griffin', '.', ''),
                 repeat('#',26),
                'abcdefghijklmnopqrstuvwxyz'),'#',''),' ','.') || '.'
  from t1
　
REPLA
-----
S.G

最后，在姓名首字母的末尾添加英文句号。
Oracle 和 PostgreSQL
REPLACE 函数会删除姓名里出现的英文句号（因为有时候 Middle Name 会以首字母形式表示），而 TRANSLATE 函数会把非大写字母都替换为字符 #。

select translate(replace('Stewie Griffin','.',''),
                 'abcdefghijklmnopqrstuvwxyz',
                 rpad('#',26,'#'))
  from t1
　
TRANSLATE('STE
--------------
S##### G######

此时，除了首字母外，姓名的其他部分都变成了 #。然后使用 REPLACE 函数删除掉所有的 #。

select replace(
       translate(replace('Stewie Griffin','.',''),
                 'abcdefghijklmnopqrstuvwxyz',
                  rpad('#',26,'#')),'#','')
  from t1
　
REP
---
S G

再次使用 REPLACE 函数把空格替换为英文句号。

select replace(
       replace(
     translate(replace('Stewie Griffin','.',''),
               'abcdefghijklmnopqrstuvwxyz',
                rpad('#',26,'#') ),'#',''),' ','.') || '.'
  from t1
　
REPLA
-----
S.G

最后，在姓名首字母的末尾添加英文句号。
MySQL
内嵌视图 Y 用于删除姓名中出现的英文句号。内嵌视图 X 可以找出姓名中空格符的个数，以便调用适当次数的 SUBSTR 函数来提取首字母。先后三次调用 SUBSTRING_INDEX 函数，根据空格的位置把字符串拆成三个单独的部分。本例中出现的姓名只包括 First Name 和 Last Name，CASE 语句的 ELSE 部分代码会被执行。

select substr(substring_index(name, ' ',1),1,1) as a,
       substr(substring_index(name,' ',-1),1,1) as b
  from (select 'Stewie Griffin' as name from t1) x
　
A B
- -
S G

如果问题中的姓名包含 Middle Name 或其首字母，那么执行下面的代码可以得到首字母。

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

上面的查询先找出 First Name 的结束位置，并前进两个字符位置移动到 Middle Name 或其首字母的开始位置；计算结果将作为 SUBSTR 函数的开始位置。因为只需要保留第一个字符，所以 Middle Name 或其首字母能被成功地返回。然后，提取出的首字母会传递给 CONCAT_WS 函数，这样就能用英文句号分割各个首字母。

select concat_ws('.',
                 substr(substring_index(name, ' ',1),1,1),
                 substr(substring_index(name,' ',-1),1,1),
                 '.' ) a
  from (select 'Stewie Griffin' as name from t1) x
　
A
-----
S.G..

最后，删除首字母中无关的英文句号。