CODEWITHSUNDEEP
sqlsql-servert-sql
user1800674
user1800674

Reputation: 233

"LIKE" and "IN" select condition SQL

I get a list of random words which I need to match against any word in a description or other text fields and those words don't have to be contiguous. This should return me any match. If you think about it it's like any word match search functionality.

String that I get back, I split on an empty space which results in a table that contains separate words for each row.

How can I reuse that table to find any match on any word since I can't use WHERE IN clause.

DECLARE @split TABLE(word NVARCHAR(2000))

SELECT *
          FROM StockSummary
         WHERE Reference in (select word from @split)
            OR Name in (select word from @split)
            OR AlternativeReference in (select word from @split)
            OR InternalReference in (select word from @split)

Upvotes: 0

Views: 188

Answers (2)

Cade Roux
Cade Roux

Reputation: 89661

Not terribly efficient, but I think this is what you are looking for. Better ways to do this, with full-text indexing.

DECLARE @split TABLE(word NVARCHAR(2000));

SELECT DISTINCT ss.*
FROM StockSummary ss
INNER JOIN @split s
    ON ss.Reference LIKE '%' + s.word + '%'
    OR ss.Name LIKE '%' + s.word + '%'
    OR ss.AlternativeReference LIKE '%' + s.word + '%'
    OR ss.InternalReference LIKE '%' + s.word + '%';

Note this is basically a cross join in execution.

To find rows which contain all words:

DECLARE @split TABLE(word NVARCHAR(2000));

SELECT Reference, Name, AlternativeReference, InternalReference
FROM (
    SELECT DISTINCT ss.Reference, ss.Name, ss.AlternativeReference, ss.InternalReference, s.word
    FROM StockSummary ss
    INNER JOIN @split s
        ON ss.Reference LIKE '%' + s.word + '%'
        OR ss.Name LIKE '%' + s.word + '%'
        OR ss.AlternativeReference LIKE '%' + s.word + '%'
        OR ss.InternalReference LIKE '%' + s.word + '%';
)
GROUP BY Reference, Name, AlternativeReference, InternalReference
HAVING COUNT(*) = (SELECT COUNT(*) FROM @split);

The DISTINCT is to handle duplicates if you have the same word in your list twice, it would not need to be twice in the results (you'd have to do more with CHARINDEX to do that).

Upvotes: 2

djangojazz
djangojazz

Reputation: 13232

DECLARE @Test TABLE (TestId INT IDENTITY, Test VARCHAR(8), Test2 VARCHAR(8), Test3 VARCHAR(8));

INSERT INTO @Test (Test, Test2, Test3) VALUES ('Duck', NULL, NUll), (NULL, NULL, 'Duck'), ('Sit', NULL, NULL), ('Kneel', NULL, NULL), (NULL, 'Hey', NULL);

--Data as is
Select *
From @Test

--Insert in some hunting data into a table
DECLARE @Find TABLE (word VARCHAR(8));
INSERT INTO @Find (word) VALUES ('Duck'),('Sit');

--match cte on multiple join conditions
; With x as 
    (
    SELECT
        a.*
    ,   CASE WHEN b.Word IS NOT NULL OR c.word IS NOT NULL OR d.word IS NOT NULL THEN 1 ELSE 0 END AS Found
    From @Test a
        LEFT JOIN @Find b ON a.Test = b.Word
        LEFT JOIN @Find c ON a.Test2 = c.Word
        LEFT JOIN @Find d ON a.Test3 = d.Word
        )
Select *
From x
WHERE Found = 1

Upvotes: 0

Related Questions