CODEWITHSUNDEEP
mysqlsql
apacheflow11
apacheflow11

Reputation:

MySQL join based on regexp

I have a table products where one of the columns are relatedproducts. relatedproducts contains strings of concatenated product IDs (column productid) separated by colon, e.g. abc-123:foo-prod:ada69, etc. Due to some bad design, there is the case that a product may be removed from products table and still be referenced in the relatedproducts column.

So I need a sql query that goes through all rows in products table, checks the relatedproducts column by exploding the data (hence the exploding the the title) and sees if each referenced product exists in the same products table. However, I am a novice at sql and having trouble writing the join/regexp query to do this.

Any help will be appreciated!

Upvotes: 1

Views: 1505

Answers (1)

Quassnoi
Quassnoi

Reputation: 425713

MySQL can match regexp's, but unfortunately cannot return the matched substring.

You better do it using FIND_IN_SET:

SELECT  *
FROM    products p
JOIN    product rel
ON      FIND_IN_SET(rel.id, REPLACE(p.related, ':', ','))

Upvotes: 3

Related Questions