When I built this blog, I decided I didn’t want to have to be putting BR tags in every time I made a post. I didn’t want to be composing an HTML document every time I posted. The most fundamental step I took in that regard is wrapping every post in nl2br, which simply converts newlines to BR tags. I have it set to do it on render, so that my content has less HTML in it in storage, which is good for use in other places.

The catch is that I’ve decided I want to use BRs instead of newlines. I’m thinking of wrapping my input boxes in HTMLarea, a nice WYSIWYG HTML editor.

So my question is, how can I convert every newline in my posts field to BR? Anyone?

3 thoughts on “SQL dilemma

  1. I’ve not tested it, but I suspect something like:

    UPDATE your_table SET field = REPLACE(field,’rn’,’n’);
    UPDATE your_table SET field = REPLACE(field,’n’,’

    would do the trick. The first statement isn’t necessary if all line-breaks are set as ‘rn’ (in which case, add r to the second statement and don’t use the first) or all line-breaks are simply ‘n’ (in which case the second statement will work alone).

    As I said, I’ve not tested it, so do backup before trying anything!

  2. Another trick that I’ve used in the past is to mysqldump your database to a file. Then, you can use perl to make any changes you need. Then slurp it back into mysql. Of course, the REPLACE option is much cleaner.

Leave a Reply

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