CODEWITHSUNDEEP
sqlsql-like
Gordan84
Gordan84

Reputation: 33

Making LIKE statement with multiple values

I have a list of partial ids that I need to match with full ids in SQL. The basic idea is following:

SELECT * 
FROM Table_name
WHERE 
    Column1 LIKE %expression_1%
    OR Column1 LIKE %expression_2%
    OR Column1 LIKE %expression_3%

The problem is that I have around 1.5k of these expressions and I am looking for more optimal way to check the whole list.

I am limited to SQL at the moment.

Upvotes: -3

Views: 48

Answers (1)

Jeff Bootsholz
Jeff Bootsholz

Reputation: 3068

Rather than hardcoding the expressions in the query, you can store the list of partial IDs in a temporary table or a derived table. This allows the database engine to optimize the matching process, especially if you index the temporary table.

SQL:

-- Create a temporary table to store the partial IDs
CREATE TEMPORARY TABLE PartialIDs (PartialID VARCHAR(255));

-- Insert your partial IDs into the temporary table
INSERT INTO PartialIDs (PartialID) VALUES
('%expression_1%', '%expression_2%', '%expression_3%', ...);

-- Match against the main table
SELECT *
FROM Table_name t
JOIN PartialIDs p
ON t.Column1 LIKE p.PartialID;

This approach avoids a long OR chain and allows the database to handle the matching more efficiently.

Upvotes: 1

Related Questions