Num6 Num6 - 1 month ago 13
MySQL Question

How to get initials from fullname in MySQL?

I have a simple table:

id | fullname
---------------------------
1 | Ivanov Ivan Ivanovich
---------------------------
2 | Petrov Petr Petrovich
---------------------------
3 | Alym kyzy Ainura


I need transform them into something like this:

id | name_initials
--------------------
1 | Ivanov I. I.
--------------------
2 | Petrov P. P.
--------------------
3 | Alym k. A.


I can easily make it via PHP, but MySQL is about data manipulation. I think, it might (and MUST) be done with DBMS only.

How can it be done via pure SQL?
Should I write stored function for this, or is there any shorter way?

Regards.

Answer

You could use a query like this:

SELECT
  fullname,
  CONCAT_WS(' ',
    SUBSTRING_INDEX(fullname, ' ', 1),
    CASE WHEN LENGTH(fullname)-LENGTH(REPLACE(fullname,' ',''))>2 THEN
      CONCAT(LEFT(SUBSTRING_INDEX(fullname, ' ', -3), 1), '.')
    END,
    CASE WHEN LENGTH(fullname)-LENGTH(REPLACE(fullname,' ',''))>1 THEN
      CONCAT(LEFT(SUBSTRING_INDEX(fullname, ' ', -2), 1), '.')
    END,
    CASE WHEN LENGTH(fullname)-LENGTH(REPLACE(fullname,' ',''))>0 THEN
      CONCAT(LEFT(SUBSTRING_INDEX(fullname, ' ', -1), 1), '.')
    END) shortname
FROM
  Names

Please see fiddle here. This query will support at maximum four names.