Using Replace in Mysql with Regex

Question

I discovered a # of records in my table where a field seems to have carriage returns. I found them using:

Select Field from Table WHERE Field REGEXP "\r\n"

I'd like to remove them but using regex in replace did not work:

Update Table set Field=Replace(Field,REGEXP "\r\n",'') where Field REGEXP "\r\n"

As an aside I have found several fields that did NOT match the regex query but still show up in the memo field as broken e..g

Queen

Anne

vs

Queen Ann

Is there any other Regex character I should be adding so I can search on any/all combinations and replace where I am not getting simply a space?

MySQL does not natively support regex replacement. It is possible using a user-defined function, in the other answers I linked. [Also here...](http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql/6216513#6216513) — Michael Berkowski, Nov 01 '15 at 16:12

score 0 · Answer 1 · answered Nov 01 '15 at 16:13

0

You just want replace():

Update Table
    set Field = Replace(Field, '\r\n', '')
    where Field REGEXP '\r\n';

MySQL should recognize '\r' and '\n' in any string (see here).

answered Nov 01 '15 at 16:13

Gordon Linoff

1 Answers1