SQL Server: Comparing a list of strings with wildcards using LIKE

Question

I'm trying to find a way to compare a character field with a few string wildcards stored in a lookup table. For example, Table A is my main table. There is a column called "Code" in Table A which I need to evaluate. I want to find all rows WHERE Code LIKE "ABC%", "A%", or "A12%". These three strings are stored in a lookup table called Table B. I am trying to avoid hard-coding these wildcards at all costs. Is there a way to do something like WHERE A.Code LIKE (SELECT * FROM B) ?

Thank you!

score 1 · Accepted Answer · answered Nov 20 '17 at 17:41

1

You do it like below using INNER JOIN:

SELECT TableA.* FROM TableA 
INNER JOIN TableB 
ON TableA.Code LIKE TableB.Code + '%'

answered Nov 20 '17 at 17:41

Md. Suman Kabir

5,243
5
25
43

Thanks! This works. But I stored the "%" in the strings in the lookup table. So I don't need this in my LIKE clause. But I get the idea and it works! – Yao Z. Nov 20 '17 at 18:02
Storing `operator` in table is not a good idea, you should just store the values and can use the query like i provided. Don't forget to follow [What should I do when someone answers my question?](https://stackoverflow.com/help/someone-answers) – Md. Suman Kabir Nov 20 '17 at 18:04
Can you tell me why it isn't a good idea to store operator in tables? – Yao Z. Nov 20 '17 at 22:22

score 0 · Answer 2 · answered Nov 20 '17 at 17:33

0

You could use JOIN:

SELECT DISTINCT a.*
FROM tabA a
JOIN tabB b
  ON a.Code LIKE b.col_name;

Rextester Demo

answered Nov 20 '17 at 17:33

Lukasz Szozda

162,964
23
234
275

SQL Server: Comparing a list of strings with wildcards using LIKE

2 Answers2