mysql - How to abbreviate row of strings with a multiple character delimiter?

Question

I have a table with a column that looks like the below:

+----------------+
| name           |
+----------------+
| Tim^*^Abas     |
| Roy^*^Techs    |
| Amanda^*^Futer |
| ...            |
+----------------+

It corresponds to first name, last name, with three characters ^*^ joining them.

I want to change this table to abbreviate each name so it takes the first letter of each first name, last name. The final column might look like:

+----------------+
| name           |
+----------------+
| T.A.           |
| R.T.           |
| A.F.           |
| ...            |
+----------------+

Can this be done strictly in SQL?

Answer 1

Here is one option:

concat(left(name, 1), '.', substring(name, locate('^*^', name) + 3, 1), '.')

If you wanted an update query:

update mytable
set name = concat(left(name, 1), '.', substring(name, locate('^*^', name) + 3, 1), '.')