How do I remove first line from text and only the first?

Posted on

Question :

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

value: [cookie] : butter – 1 cup. [cookie] : flour – 2 cup. [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 [cookie] : butter – 1 cup.


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| [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.

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

It’s kinda hard to tell from your question & example you provided; you say you want to update but your example shows a select. You also don’t mention version of mysql you’re using.

However, based on what you said you wanted & the substring_index you’ve used, you can use:

    UPDATE Table1
    SET Value = REPLACE(value,
    SUBSTRING_INDEX(value, 'n', 1),''); 

to replace everything up to the first found newline with nothing.

This is straight from

I’m not a regular user of mysql, but a quick google search for mysql string functions turned up this tutorial.

Leave a Reply

Your email address will not be published. Required fields are marked *