Recipe6.7.Extracting Initials from a Name

Recipe 6.7. Extracting Initials from a Name

Problem

You want convert a full name into initials. Consider the following name:

  Stewie Griffin 

You would like to return:

  S.G. 

Solution

It's important to keep in mind that SQL does not provide the flexibility of languages such as C or Python; therefore, creating a generic solution to deal with any name format is not something particularly easy to do in SQL. The solutions presented here expect the names to be either first and last name, or first, middle name/middle initial, and last name.

DB2

Use the built-in functions REPLACE, TRANSLATE, and REPEAT to extract the initials:

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

MySQL

Use the built-in functions CONCAT, CONCAT_WS, SUBSTRING, and SUBSTRING_ INDEX to extract the initials:

  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 and PostgreSQL

Use the built-in functions REPLACE, TRANSLATE, and RPAD to extract the initials:

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

SQL Server

As of the time of this writing, neither TRANSLATE nor CONCAT_WS is supported in SQL Server.

Discussion

By isolating the capital letters you can extract the initials from a name. The following sections describe each vendor-specific solution in detail.

DB2

The REPLACE function will remove any periods in the name (to handle middle initials), and the TRANSLATE function will convert all non-uppercase letters to #.

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

At this point, the initials are the characters that are not #. The function REPLACE is then used to remove all the # characters:

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

The next step is to replace the white space with a period by using REPLACE again:

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

The final step is to append a decimal to the end of the initials.

Oracle and PostgreSQL

The REPLACE function will remove any periods in the name (to handle middle initials), and the TRANSLATE function will convert all non-uppercase letters to '#'.

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

At this point, the initials are the characters that are not "#". The function REPLACE is then used to remove all the # characters:

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

The next step is to replace the white space with a period by using REPLACE again:

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

The final step is to append a decimal to the end of the initials.

MySQL

The inline view Y is used to remove any period from the name. The inline view X finds the number of white spaces in the name so the SUBSTR function can be called the correct number of times to extract the initials. The three calls to SUBSTRING_ INDEX parse the string into individual names based on the location of the white space. Because there is only a first and last name, the code in the ELSE portion of the case statement is executed:

  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 

If the name in question has a middle name or initial, the initial would be returned by executing

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

which finds the end of the first name then moves two spaces to the beginning of the middle name or initial; that is, the start position for SUBSTR. Because only onecharacter is kept, the middle name or initial is successfully returned. The initials are then passed to CONCAT_WS, which separates the initials by a period:

  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.. 

The last step is to trim the extraneous period from the initials.