SQL Substring in WHERE clause

Question

I'm trying to build a SQL query that will show me all the products that have products.description IS NULL but also have a corresponding description in the descriptions table. So code is always a substring of info.

The products.code is a substring of descriptions.info, so I'm not sure how to pull this.

products

id | code | description
--------------------------
1 12345-1 NULL

descriptions

id | info
---------------------------
1 12345-1_Hammer

• Nic: Is the substring always the same length
• A_B: @nic yes always the same length

Answer 1

You need t o compare product.info with descriptions.info. If the needed condition is for one to be a substring of the other, it can be done with LIKE.

Since there may be more than one descriptions matching (having as substring) a product code, we should use an EXISTS subquery:

SELECT p.id, p.code
FROM products AS p
WHERE p.description IS NULL
 AND EXISTS
 ( SELECT *
 FROM descriptions AS d 
 WHERE d.info LIKE '%' + p.code + '%'
 ) ;

Answer 2

select distinct * 
from products 
join descriptions 
 on products.description is null 
 and products.code + '%' like descriptions.info