MySQL: select the first element of a comma separated list.

Question

Unfortunately, I have a field column that contains values ​​such as

• 4
• 12.3
• 8,5,6,7

I am going to write a SELECT statement that will result in:

• 4
• 12
• 8

How can I do this in practice since MySQL does not provide a split function?

Answer 1

Use the MySQL function SUBSTRING_INDEX :

 SELECT SUBSTRING_INDEX(field, ',', 1) 

However, storing lists in strings separated by delimiters is usually an inefficient use of a relational database management system such as MySQL: it is often better to normalize your data structure by storing such lists in a separate table of pairs (id, value) .

Answer 2

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

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

Answer 3

try it

 substring(str,1,instr(str,',')-1)