MySQL REGEXP not working

Question

I am trying to update my product database and find duplicates after some product numbers were simplified.

Here is what I am trying that is not working:

SELECT b.Id as OLDID, c.Id as NEWID
FROM productdata b, productdata c
WHERE b.ProdNum REGEXP CONCAT('^', c.ProdNum, '(\-|\s)[a-zA-Z0-9]+(\-|\s)[a-zA-Z0-9]+')

Old Product number: WR-101 3 Day (as well as WR-101 1 Day, etc.)

New Product number: WR-101

So basically I need a where clause that will match up the new Product number with the old product number that had "1 Day", "3 Day" etc. on them.

Thanks for your help.

I know the REGEX is more complex than it needs to be, but I ok with the extra resources and I want one that can catch more than this specific problem. — EricImprint
– EricImprint, Commented Mar 6, 2013 at 0:04

Explosion Pills · Accepted Answer · 2013-03-06 00:08:56Z

3

MySQL's regex flavor does not support \s for spaces. You need to use [[:space:]]. You also don't need to escape the dash:

(-|[[:space:]])[a-zA-Z0-9]+(-|[[:space:]])[a-zA-Z0-9]+

answered Mar 6, 2013 at 0:08

Explosion Pills

192k56 gold badges341 silver badges417 bronze badges

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

1 Comment

Olaf Dietsche Over a year ago

+1 You can further simplify with [-[:space:]] instead of (-|[[:space:]])

Collectives™ on Stack Overflow

MySQL REGEXP not working

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related