TSQL using table variable for like comparison, ideally without iterating

Question

Following the posting,

TSQL Variable With List of Values for IN Clause

I formed a table variable of varchar, e.g.

    Declare @myList_1(Id varchar(2))
Insert into @myList_1
Select id from  (VALUES ('x1')) AS tbl(id)
Insert into @myList_1
Select id from  (VALUES ('x2')) AS tbl(id)
Insert into @myList_1
Select id from  (VALUES ('x3')) AS tbl(id)    

However, I need to extend this concept so that I can do something such as a "like comparison"

e.g.:

myTable.myCol like ('%' +  @myList_1 + '%')

above: SSMS 2008 r2 gives error: 'Must declare the scalar variable @myList_1'

If possible, I'd like to do this without an iterator or looping construct ...

Answer 1

Let's assume following testing data and tables structure.

-- Create test table
CREATE TABLE [dbo].[myTable](
    [Id] [int] NOT NULL PRIMARY KEY,
    [myCol] [varchar](100) NULL)
GO

-- Insert test data
INSERT INTO myTable(Id, myCol)
VALUES (1, 'Bladder cancer'),
    (2, 'Lung cancer'),
    (3, 'Brain cancer'),
    (4, 'Melanoma'),
    (5, 'Breast cancer'),
    (6, 'Non-Hodgkin lymphoma'),
    (7, 'Cervical cancer'),
    (8, 'Ovarian cancer'),
    (9, 'Cardiovascular disease'),
    (10, 'Nerve damage'),
    (11, 'Kidney damage'),
    (12, 'Eye damage'),
    (13, 'Foot damage'),
    (14, 'Skin conditions'),
    (15, 'Alzheimer''s disease'),
    (16, 'Hearing impairment')
GO

Now the actual SQL would be based on joining the table where you want to do search with table variable @myList_1

-- Create list of items to look for
Declare @myList_1 table(Id varchar(20))
Insert into @myList_1
Select id from  (VALUES ('cancer')) AS tbl(id)
Insert into @myList_1
Select id from  (VALUES ('Melanoma')) AS tbl(id)
Insert into @myList_1
Select id from  (VALUES ('lymphoma')) AS tbl(id)    

-- Do query
SELECT myTable.Id, myTable.myCol
FROM myTable
JOIN @myList_1 as ItemsList
ON myTable.myCol like '%' + ItemsList.Id + '%'

In this simple case where myCol could match just single value in the @myList_1 this should be enough, but for cases where possible that myCol could match multiple values in the @myList_1 you should add DISTINCT to the query

-- Do query
SELECT DISTINCT myTable.Id, myTable.myCol
FROM myTable
JOIN @myList_1 as ItemsList
ON myTable.myCol like '%' + ItemsList.Id + '%'