Using Regex in mySQL query

Question

I have a database column with multiple names separated by line breaks like this:

jones, sue
jones, mark

I want to generate a query that keeps the first line and discards the rest, no matter how many lines there are...

I though I was getting close with this:

SELECT 
 name1 
FROM 
 email 
WHERE 
 name1 REGEXP '.*(\r\n)?'

but not quite...

Answer 1

SELECT SUBSTRING_INDEX(col, "\r\n", 1) ...

Test case:

mysql> SELECT SUBSTRING_INDEX("jones, sue\r\njones, mark", "\r\n", 1);
+---------------------------------------------------------+
| SUBSTRING_INDEX("jones, sue\r\njones, mark", "\r\n", 1) |
+---------------------------------------------------------+
| jones, sue |
+---------------------------------------------------------+

• C.Lamb: hmmmm. thanks, but that doesn't seem to work... Is it possible that \n and \r (tried both...) don't work with my particular data? the column is utf-8 encoded...
• Rick James: @C.Lamb - I added a test case. If you still have troubles, please provide your failing case. (ascii/latin1/utf8 -- all work the same in this example.)