Using [like] like in clause in SQL?

Question

USE tempdb

CREATE TABLE A
(
     id INT,
     a_desc VARCHAR(100)
)

INSERT INTO A 
VALUES (1, 'vish'),(2,'hp'),(3,'IBM'),(4,'google')

SELECT * FROM A

CREATE TABLE B
(
     id INT,
     b_desc VARCHAR(100)
)

INSERT INTO B 
VALUES (1, 'IBM[SR4040][SR3939]'),(2,'hp[GR3939]')

SELECT * FROM B

SELECT * 
FROM A 
WHERE a_desc LIKE (SELECT b_desc FROM B) -- IN with LIKE problem here

all the time the ending string is not same in table B so I can't use trim approach to delete certain character and match in In clause.

-- above throwing error subquery returned more than 1 value

-- I've thousand rows in both tables just for example purpose I've created this example

--excepted output 
--IBM
--hp 

--from A table

Answer 1

Try this one -

Query:

SELECT * 
FROM A
WHERE EXISTS(
    SELECT 1
    FROM B
    WHERE b_desc LIKE '%' + a_desc + '%'
)

Output:

id          a_desc
----------- ----------
2           hp
3           IBM

Execution plan:

Extended statistics:

Update:

SELECT A.*, B.* 
FROM A
OUTER APPLY (
     SELECT * 
     FROM B
     WHERE b_desc LIKE '%' + a_desc + '%' 
) B
WHERE b_desc IS NOT NULL

Answer 2

you can simple join:

SELECT distinct a.* 
from A inner join b on b.b_desc like '%' + a.a_desc + '%'