title: MySQL join based on regexp
body: <p>I have a <strong>table products</strong> where one of the <strong>columns are relatedproducts</strong>. relatedproducts contains strings of concatenated product IDs (<strong>column productid</strong>) 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.</p>
  
  <p>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.</p>
  
  <p>Any help will be appreciated!</p>