Replace column with just first line

Forum Moderators: open

Message Too Old, No Replies

Replace column with just first line

barns101

3:48 pm on May 26, 2007 (gmt 0)

I have a field that contains text that currently stretches over a few paragraphs, separated with normal line breaks. I'd like to update this field with just the current first line but can't think where to start because (as far as I know) the MySQL replace() function only allowed for known values to be changed. All of my fields are going to contain different values.

Using PHP I could select all records, explode the field at "\r\n" and then update the field with the first array value. Is there any native MySQL function that would allow me to do it more simply?

FalseDawn

4:48 pm on May 26, 2007 (gmt 0)

Something like:

UPDATE table SET field=LEFT(field,INSTR(field, '\r\n')-1)

You may have to play with the newline characters to get this to work.

Familiarity with MySql String functions is a good thing to have:
[dev.mysql.com...]

Edit: Of course, this may fail if the string does not actually contain more than one line... a CASE construct should be able to take care of that easily enough.
[dev.mysql.com...]

[edited by: FalseDawn at 4:52 pm (utc) on May 26, 2007]

barns101

6:27 pm on May 26, 2007 (gmt 0)

Thank you very much FalseDawn, I'll read up on LEFT() and INSTR(). I had a look at the string functions before posting but had no idea where to start.