CODEWITHSUNDEEP
mysqlregexconcatenation
EricImprint
EricImprint

Reputation: 177

MySQL Regular Expression Match

I am trying to update my product database and find duplicates after some product numbers where changed.

In the past the changed item numbers just had an extra dash and number or letter on the end and I used this where clause:

where a.ProdNum REGEXP CONCAT('^', b.ProdNum, '(\-|\s)[a-zA-Z0-9]+')

Now the REGEXP is over my head.

Old Product number: BRB-0325

New Product number: 0325-15 (the number after the dash can be any 1 or 2 digit number)

So basically I need a where clause that will drop the "BRB-" from the Old Number and drop any 1 or 2 digit number after the new item number.

I can do the dropping of the "BRB-", but not sure how to handle dealing with the 1-2 digit number, and I really don't know how to do both at the same time.

where b.ProdNum = CONCAT('BRB-', c.ProdNum)

Here is what I tried with the 2 digits and that didn't work.

where c.ProdNum REGEXP CONCAT('^', b.ProdNum, '(\-)[0-9]{1,2}+')

Thanks for your help.

Upvotes: 0

Views: 212

Answers (3)

lc.
lc.

Reputation: 116528

Assuming c.ProdNum is the new number and b.ProdNum is the old:

WHERE CONCAT('BRB-', c.ProdNum) REGEXP CONCAT('^', b.ProdNum, '-[0-9]{1,2}$')

This adds "BRB-" to the new number and compares it to the old. So you will be essentially comparing "BRB-0325-15" with "BRB-0325-nn".

Upvotes: 2

Ross Smith II
Ross Smith II

Reputation: 12189

This should work for you:

SELECT
old.*
FROM
products old,
products new
WHERE new.ProdNum REGEXP CONCAT('^', SUBSTRING(old.ProdNum, 5), '-[0-9]{1,2}$')
AND   old.ProdNum LIKE 'BRB-%'

This should use an index on ProdNum if one is available.

Upvotes: 0

Bohemian
Bohemian

Reputation: 425278

Why are you using REGEXP? Use a straightforward comparison:

where a.ProdNum like concat(substring(b.ProdNum, 4), '-??')

This will match 0325-ab, but surely you don't have such new product numbers.

Upvotes: 2

Related Questions