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.
SELECT SUBSTRING('Frank Thomas',1,1) + '.' + SUBSTRING('Frank Thomas',CHARINDEX(' ','Frank Thomas')+1,1) + '.' AS Initials; Initials F.T.
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.