19 Apr

SQL Cookbook: Initials from a name.

I’m pretty sure this wouldn’t come up very often but it’s an example the author used and he did it with an awful lot of code.  Essentially you want a name like, “Frank Thomas” to be translated to “F.T.”.  Now, assuming that you don’t have any middle initials (the author’s examples do consider it, while mine do not), long multi-part names, here is all you would need to do.

SQL Server

SELECT SUBSTRING('Frank Thomas',1,1) + '.' +
SUBSTRING('Frank Thomas',CHARINDEX(' ','Frank Thomas')+1,1) + '.' AS Initials;

Initials
F.T.

MySQL

mysql> SELECT CONCAT(SUBSTRING('Frank Thomas',1,1), '.', SUBSTRING('Frank Thomas
',LOCATE(' ','Frank Thomas')+1,1), '.') AS Initials;
+----------+
| Initials |
+----------+
| F.T.     |
+----------+
1 row in set (0.00 sec)

The two versions do the same thing but they CONCAT differently and use a different function. First, CHARINDEX (SQL Server) is the equivalent of LOCATE (MySQL), which is the equivalent of INSTR (MySQL).

Yeah, no wonder I routinely blow syntax questions in interviews.

MySQL performs a concatenation with the CONCAT function and it’s essentially a list of items to concatenate. The author talks about using CONCAT_WS, but, that acts as a separator and in this case we need the period at the end, which CONCAT_WS, does not do, however, if I were going after a middle initial it would probably be useful.

SQL Server, however, allows you to concatenate with a simple plus (+) sign.

3 thoughts on “SQL Cookbook: Initials from a name.

Leave a Reply

Your email address will not be published. Required fields are marked *