SQL LIKE multiple values

Question

I am running the following sql code in python :

SELECT 
    FIN AS 'LIN',
    CUSIP, 
    Borrower_Name, 
    Alias,
    DS_Maturity, 
    Spread, 
    Facility, 
    Facility_Size, 
    Log_date
FROM 
    [Main].[FacilityInformation]
WHERE 
    CUSIP IN ('00485GAC2', 'N1603LAD9')
    OR (YEAR(DS_Maturity) in (2019,2024)
    AND ((Borrower_Name LIKE 'Acosta Inc , Bright Bidco BV%'
          OR Alias LIKE 'Acosta 9/14 (18.61) Non-Extended Cov-Lite, Lumileds 2/18 Cov-Lite%')))

It works perfectly when I have 3 or 4 borrower names or cusips or alias, but I am trying to run this with dozens of possible values. I thoungh that following the same logic as IN ('{}') with LIKE '{}%' will work, but it doesn't. So I want to use a efficient code but not something like :

SELECT * FROM table WHERE
column LIKE 'text1%'
column LIKE 'text2%'
.
.
.
column LIKE 'textn%'

This is good if every time you know the exactly numbers of time that you have to introduce the 'text, even though it is hard to do this 30 times or more so it will be pretty bad for a large number of borrower_names or cusips. It is not efficient. I hope it is clear what I am trying to ask.

If you are using SQL Server, then you should remove the MySQL tag. — Tim Biegeleisen, Oct 24 '19 at 13:54
What is the source of values to be compared against the various columns? — Tim Biegeleisen, Oct 24 '19 at 13:55
You should use parameterized queries and then instead of lists of values you should pass in table valued parameters. — Sean Lange, Oct 24 '19 at 13:56
@TimBiegeleisen the source is an excel input with borrower name, cusip, alias and year columns — Gabriel, Oct 24 '19 at 13:57
@Gabriel . . . One of your previous questions was tagged sql-server so I removed the mysql tag. — Gordon Linoff, Oct 24 '19 at 13:59

score 1 · Accepted Answer · answered Oct 24 '19 at 14:25

You can join using VALUES and LIKE:

-- Test data
DECLARE @s TABLE (mytext NVARCHAR(20))
INSERT INTO @s VALUES ('abc'), ('def'), ('ghi')

-- Select
SELECT
    s.mytext
FROM @s s
INNER JOIN (VALUES ('a%'),('%h%')) n(wildcard) ON s.mytext LIKE n.wildcard

Or you can do it by using a table:

DECLARE @s TABLE (mytext NVARCHAR(20))
DECLARE @t TABLE (wildcard NVARCHAR(20))

INSERT INTO @s VALUES ('abc'), ('def'), ('ghi')
INSERT INTO @t VALUES ('a%'), ('%h%')

SELECT s.mytext FROM @s s
INNER JOIN @t t ON s.mytext LIKE t.wildcard

Both gives the result:

mytext
------
abc
ghi

SQL LIKE multiple values

1 Answers1