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... — Michael Berkowski
– Michael Berkowski, Commented Nov 1, 2015 at 16:12
Why did you think you needed a regexp? You're replacing a fixed string, not a pattern. — Barmar
– Barmar, Commented Feb 15, 2024 at 23:08

Gordon Linoff · Accepted Answer · 2015-11-01 16:13:21Z

1

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 1, 2015 at 16:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1