Search code examples
sqlsql-serversubstringcharindex

SQL query for parsing a body of text to extract a string from a list

I need help on an SQL query to perform the following:

I have a table with a list of possible string values for products.

I have a second table with free form text in which this product may be mentioned. Is there any way an SQL query can extract the string if it is present in the 1st table?

I read on another SO post about CHARINDEX and SUBSTRING. Will this be efficient in this scenario? How can i apply this in my use case?

AN example for my scenario is this:

My table, PRODUCTS, has the following format,

Product
 XXX
 YYY
 ZZZ
 DDD

The other table has a column in which there is large amount of text in which this product will be mentioned. Like:

Record Number    User Review

      1           I like XXX for its versatility but YYY is better. 
      2           XXX is a horrible product. DO not buy.
      3           YYY and DDD are best in class. Many do not know how to use it.

Now I want to extract the product names using a query in this manner.

Record Number    Product in Review
      1                XXX
      1                YYY
      2                XXX
      3                YYY
      3                DDD

Thank you in advance for your time and help.

Solution

  • This should work but it will be slow on big tables:

    Select p.id, f.id, p.name from product
Inner Join freeform f on f.text like '%'+p.name+'%'