CODEWITHSUNDEEP
mysql
kiss-o-matic
kiss-o-matic

Reputation: 1181

MySQL: Embedded (not trailing) newline characters

I have a bit of an issue. I've got an old DB that I'm using in an AngularJS site. Angular doesn't like (read: chokes on) the newline characters in the text columns, and honestly I don't need them. So, I'm happy to replace them with REPLACE. The caveat is that REGEXP or REPLACE doesn't seem to find "\n" characters (or "\r\n") for that matter if they are embedded.

When I do

SELECT * FROM my_table WHERE text_column REGEXP '.*\n.*'

I only get a couple of hits which I believe end in a newline character.

UPDATE my_table SET text_column = REPLACE( text_column, "\n", "" )

It only changes the trailing ones (presumably). I could be missing something.

Thoughts?

EDIT: As per CBroe's suggestion, I dumped the contents and checked them in vim. It looks like the newlines are actually ^M. According to the thread below, ^M is the way vim displays 0xD which is a windows carriage return. I can't remember ever using Windows to populate the data but I suppose it's not unheard of. (It would have been 10 plus years ago). Anywho, how would I match/replace this character? REGEXP won't catch 0xD AFAIK.

Upvotes: 1

Views: 119

Answers (1)

vadchen
vadchen

Reputation: 1462

I would solve this problem outside of MySQL.

  1. Dump the table into file
  2. replace all bad character in the file (like explaned here)
  3. import the table back into the database

Upvotes: 1

Related Questions