How to simplify repeating variables in where clause for multiple columns SQL Server I know there has to be a better way to evaluate a set of repeating variables against multiple columns. I've been searching for a while and still don't have a great solution. Is there a better way to do this?

SELECT *
FROM TABLE
WHERE 
  (SUBSTRING(COLUMN1,1,3) IN ('val1','val2','val3','val4'...REPEATS to 30)  
OR SUBSTRING(COLUMN2,1,3) IN ('val1','val2','val3','val4'...REPEATS to 30)  
OR SUBSTRING(COLUMN3,1,3) IN ('val1','val2','val3','val4'...REPEATS to 30)  
OR SUBSTRING(COLUMN4,1,3) IN ('val1','val2','val3','val4'...REPEATS to 30)  
OR SUBSTRING(COLUMN5,1,3) IN ('val1','val2','val3','val4'...REPEATS to 30))   
...REPEATS 5 MORE TIMES
One good way to get reasonable performance from this type of query is to insert the 'val1', 'val2', ... values into a temporary table, then join against that temporary table.

We start with some sample data:

USE tempdb;
IF OBJECT_ID('dbo.Items') IS NOT NULL DROP TABLE dbo.Items;
CREATE TABLE dbo.Items
(
    ItemID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_Items
        PRIMARY KEY CLUSTERED
    , col1 varchar(30) NOT NULL
    , col2 varchar(30) NOT NULL
    , col3 varchar(30) NOT NULL
    , col4 varchar(30) NOT NULL
) ON [PRIMARY];

INSERT INTO dbo.Items (Col1, Col2, Col3, Col4)
VALUES ('value0', 'value02', 'value03', 'value04')
    , ('value11', 'value21', 'value31', 'value41')
    , ('value5', 'value6', 'value7', 'value8');


To find desired rows, we insert the search terms into a temporary table, then join against it:

IF OBJECT_ID('tempdb..#SearchItems') IS NOT NULL DROP TABLE #SearchItems;
CREATE TABLE #SearchItems
(
    val varchar(30) NOT NULL
        PRIMARY KEY CLUSTERED
);

INSERT INTO #SearchItems (val)
VALUES ('value2%')
    , ('value03%')
    , ('cow%');

SELECT i.*
FROM dbo.Items i
    INNER JOIN #SearchItems si 
        ON (i.col1 LIKE si.val)
            OR (i.col2 LIKE si.val)
            OR (i.col3 LIKE si.val)
            OR (i.col4 LIKE si.val);


The output from the query above:

+--------+---------+---------+---------+---------+
| ItemID |  col1   |  col2   |  col3   |  col4   |
+--------+---------+---------+---------+---------+
|      1 | value0  | value02 | value03 | value04 |
|      2 | value11 | value21 | value31 | value41 |
+--------+---------+---------+---------+---------+