CODEWITHSUNDEEP
mysql
imos
imos

Reputation: 175

Remove \n and everything afterward in mysql column

I have a mysql table like the example below. Sometimes there is an extra line a \n the column with info afterward that I do not need and I would like to remove it keeping only the first line. How would I remove these \n's and the data afterward in the column?

| column1          |
+------------------+
| name1            |
| useless info     |
+------------------+
| name2            |
+------------------+
| name3            |
+------------------+

My goal would be to end up with this:

| column1          |
+------------------+
| name1            |
+------------------+
| name2            |
+------------------+
| name3            |
+------------------+

Upvotes: 0

Views: 76

Answers (2)

juergen d
juergen d

Reputation: 204746

Use SUBSTRING_INDEX()

update your_table
set column1 = SUBSTRING_INDEX(column1 , '\n', 1)

Upvotes: 1

Juan Ruiz de Castilla
Juan Ruiz de Castilla

Reputation: 974

update 
t
set t.col1 = 
case 
 when locate("\n",col1) > 0 
  then left(col1,locate("\n",col1))
 else col1 end
from test t

Upvotes: 0

Related Questions