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 ...

For inspirations you could look for the question - stackoverflow.com/questions/26621251/… — codevision
– codevision, Commented Dec 18, 2017 at 18:35

codevision · Accepted Answer · 2017-12-18 18:35:28Z

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 + '%'

Collectives™ on Stack Overflow

TSQL using table variable for like comparison, ideally without iterating

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related