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:

    name1  REGEXP '.*(\r\n)?'

but not quite...

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                                              |
| improve this answer | |
  • 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... – C.Lamb Nov 6 '18 at 10:09
  • 1
    @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.) – Rick James Nov 6 '18 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.