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.
** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table
0 Comments:
Post a Comment