CODEWITHSUNDEEP
mysql
Syed Mudabbir
Syed Mudabbir

Reputation: 287

Replacing First Space with new line mysql

I am looking for a mysql query where i can replace first occurrence of Space in a string with a new line.

For example,

LSRNABC1234 This is a sample

So here I wish to insert new line after LSRNABC1234 so resulting string would look like: 

LSRNABC1234 
This is a sample

The regular find and replace queries replace everything, I am not sure how to insert new line just after first space.

Thanks

Upvotes: 0

Views: 88

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

Try the following update:

UPDATE yourTable
SET text = CONCAT(SUBSTRING_INDEX(text, ' ', 1),
                  CHAR(10 using utf8),     -- line break
                  SUBSTRING(text, INSTR(text, ' ') + 1));

Here is a demo showing that the string manipulation logic is working correctly:

Demo

Upvotes: 1

Related Questions