Search code examples
sqlsql-like

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.

Solution

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