MySQL: select first element of a comma-separated list

Question

Unfortunately, I have a field column which contains values like

• 4
• 12,3
• 8,5,6,7

I'm going to write a SELECT statement, whose result would be:

• 4
• 12
• 8

How can I do it in practice, since MySQL does not provide a "split" function?

Answer 1

Use MySQL's SUBSTRING_INDEX function:

SELECT SUBSTRING_INDEX(field, ',', 1)

However, keeping lists in delimiter-separated strings is generally an inefficient use of a relational database management system like MySQL: it is often better to normalise your data structure by keeping such lists in a separate table of (id, value) pairs.

Answer 2

You can use MySQL function SUBSTRING_INDEX(str,delim,count)

SELECT SUBSTRING_INDEX(value,',',1) As value FROM ...