CODEWITHSUNDEEP
mysqlstring-parsing
MC Emperor
MC Emperor

Reputation: 22997

How to select the first letter of each word from a table cell in MySQL?

How can I select the first letter of each word in MySQL using a query?

So this table

+----+----------------------------+
| id | str                        |
+----+----------------------------+
|  1 | Hello my name is MCEmperor |
|  2 | How are you doing?         |
+----+----------------------------+

would return

+----+----------------------------+
| id | str                        |
+----+----------------------------+
|  1 | HmniM                      |
|  2 | Hayd                       |
+----+----------------------------+

I guess it's something with SUBSTRING and LOCATE and maybe I need a loop (to find all spaces or something)...

Is it possible within a query? How should I do that?

Upvotes: 1

Views: 1968

Answers (2)

Pete Gardner
Pete Gardner

Reputation: 545

What you're looking for is a WHERE clause that matches only part of the data in the cell. You can do that like so:

SELECT str 
from (table name) 
WHERE str LIKE 'H%'

Upvotes: 1

Kevin Coulombe
Kevin Coulombe

Reputation: 1575

Maybe you could simply split by space? Use this stored proc : http://forums.mysql.com/read.php?60,78776,148332#msg-148332

You can then retrieve the first letters of each word and use GROUP_CONCAT in a GROUP BY Id to put the letters back into one line per initial text.

Upvotes: 1

Related Questions