TSQL Column Values used in WHERE...LIKE

Question

Okay so I currently have tables called ModTable, Slots, and I want to pass All of the values from the column [Modules] in ModTable to a LIKE. In effect, I need to search my Slots table, specifically the SlotValue column, for entries matching the entries in ModTable, which I have reformatted to be %//[Module]/%. I did it this way because the values in Slots.SlotValue that I want to pull follow the pattern of %//[Module]/%, but there are multiple modules in the column. My code looks like this:

(
    SELECT [ObjectID]
    FROM [Slots]
    WHERE SlotValue LIKE
    (
        SELECT [Module]
        FROM [ModTable]
    )
)

ModTable:

Modules
%//XYZ/%
%//ABC/%
%//LMN/%

Want:

WHERE [Slots].[SlotValue] LIKE(%//XYZ/% OR %//ABC/% OR %//LMN/%)

I can post the full code if needed; it's probably not optimal, though, and references 2 other tables, as well. — G_Zir, Jul 06 '22 at 17:57
Please read [this](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. As it stands there are some confusing singular/plural references in your question, e.g. the column `[Modules]` that is `[Module]` in the code. — HABO, Jul 06 '22 at 18:09
I am not sure what you mean by "correlated 'EXISTS'"; do you think you could show me what you mean? — G_Zir, Jul 06 '22 at 18:09
@HABO I used [Modules] to refer to the column, but I was not sure how to refer to an individual item within the [Modules] column — G_Zir, Jul 06 '22 at 18:12
If you would show us a [minimal, reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) then we wouldn't be guessing about what `NVarChar(MAX)` value is in what column, but I suspect there is a list of module names in a _single_ row in one of your tables. That would be bad. (Storing a delimited list, guessing, guessing incorrectly, ... .) — HABO, Jul 06 '22 at 18:19
Edited to show what ModTable looks like, basically, and I want to make the `LIKE` "dynamic" in that it pulls all entries in the [Modules] column. As it stands, if my `LIKE` returns more than one value, I get an error message. I am using T-SQL on MS SSMS. — G_Zir, Jul 06 '22 at 18:40
I think maybe this reference could help you. https://stackoverflow.com/questions/1398720/how-to-use-like-with-column-name — Dariush, Jul 31 '22 at 17:52

score 1 · Answer 1 · answered Jul 06 '22 at 18:36

1

Use a correlated EXISTS:

SELECT [ObjectID]
FROM dbo.[Slots] S
WHERE EXISTS
(
    SELECT 1
    FROM dbo.[ModTable] MT
    WHERE S.SlotValue LIKE MT.Module
);

answered Jul 06 '22 at 18:36

Thom A

88,727
11
45
75

If I copy and paste this in, it totally does what I was trying to do. I really appreciate your help and feedback. Also, how do the S and MT in `FROM` statements work? And the 1 in `SELECT`; I only started learning Sequel about a month ago so a lot of this is new to me. – G_Zir Jul 06 '22 at 18:48
1

@G_Zir -- they are alias names for the table the word "AS" before them is optional -- they let you use a shortcut to the fully qualified table name. – Hogan Jul 06 '22 at 18:51
1

They are aliases, @G_Zir . They are *always* a good thing to use on both your tables and to qualify your columns, especially when dealing with multiple tables. – Thom A Jul 06 '22 at 18:55

score 1 · Accepted Answer · answered Jul 06 '22 at 18:36

1

SELECT [ObjectID]
FROM [Slots]
JOIN [ModTable] ON SlotValue LIKE ModTable.Module

This might give you more than one row with the same value -- so you can use a distinct if you want.

answered Jul 06 '22 at 18:36

Hogan

69,564
10
76
117

This is exactly what I was trying to do! Thank your for your help. – G_Zir Jul 06 '22 at 18:44
@G_Zir -- no problem. This is fine for small tables but could get bad for bigger ones. Remember that regex is very slow for values starting with % and for all the examples you show CONTAINS would probably be better (faster) – Hogan Jul 06 '22 at 18:49

TSQL Column Values used in WHERE...LIKE

2 Answers2