regexp_replace() - matches but does not replace at end of line

Question

I'm trying to regexp_replace() all the values of a column ending without "/", by adding "/".

I can get the correct values by using this statement (the pattern was tested with a PCRE checker):

SELECT * FROM `table` WHERE `column` REGEXP("(?<=[^\/])$");

And the non-matching ones with:

SELECT * FROM `table` WHERE `column` REGEXP("(?<![^\/])$");

But when the statement is:

UPDATE `table` SET `column` = REGEXP_REPLACE(`column`, "(?<=[^\/])$", "/");

Then, there is no change, whatever value I put into the third parameter:

Query OK, 0 rows affected (0.00 sec)
Rows matched: 1031  Changed: 0  Warnings: 0

Have you tried something like REGEXP_REPLACE(`column`, "^(.*[^/])$", "\\1/") ? — Andrea Corbellini
– Andrea Corbellini, Commented Oct 5, 2018 at 22:37

Michał Turczyn · Accepted Answer · 2018-09-17 09:07:42Z

2

You could do this easily without regex:

UPDATE `table` SET `column` = `column` + '/'
WHERE RIGHT(`column`, 1) <> '/'

answered Sep 17, 2018 at 9:07

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

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

1 Comment

CharlesM Over a year ago

I use regexp_replace() quite often, so I was trying to understand why it does not work. Anyway, thanks for the working solution!

Rick James · Accepted Answer · 2018-10-05 22:28:20Z

0

trying to understand why it does not work

As I rationalize the problem, you are asking REGEXP_REPLACE to do two things:

Discover that something is missing, and
Point to a location in the string.

Your regexp says that it is missing, but I question whether it points to a specific substring (even an empty one) for replacing. It's easy to point to a found substring (or substrings). It is hard to point to a missing substring. And such a 'pointer' is needed to do the replacement.

Hence, Michal's approach (even if some regexp were needed) is the "right" way to solve the problem.

answered Oct 5, 2018 at 22:28

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

regexp_replace() - matches but does not replace at end of line

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest