Mysql – How to remove first line from text and only the first

MySQL

I have a tables "Table1" in this table I have a Text NOT null field – "value". the value field is populated like so:

value: 1.2.3.4 [cookie] : butter – 1 cup.

1.2.3.4 [cookie] : flour – 2 cup.

1.2.3.4 [cookie] : sugar – 1/2 cup.

I need to remove only the FIRST line and keep all others. This value can grown up to 500 lines. I am unsure how to delete only the

1.2.3.4 [cookie] : butter – 1 cup.

line.

I tried using replace and substring_index without success. This was my attempt:

SELECT name, SUBSTRING_INDEX(value, '\n', 1) FROM variables 

which returns:

Cookies| 1.2.3.4 [cookie] : butter – 1 cup.

The values are put in with new lines as the "separator". I am looking to replace the values with everything after the first string of character up to the end of the first "line". This is more of of a block of text that I need to adjust.

Please assist in removing only the first line for the value.

Best Answer

You're almost there. I think what you need is:

SELECT substring(value, LOCATE('\n', value)+1)

This means:

  • find the first newline in the string
  • then add one (to go to the next character after the newline)
  • and return everything from that position to the end of the string