CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
Deina Underhill
Deina Underhill

Reputation: 567

TSQL search for multiple conditions

I've learned that I can search multiple fields using something like: 

DECLARE @srch nvarchar(40) 
    SET @srch = '%something%'

SELECT * FROM DataTable dt
    WHERE CONCAT(dt.field1, dt.field2) LIKE @srch

But is there a way to search for multiple criteria other than with multiple ORs? 

DECLARE @srch1 nvarchar(40), @srch2 nvarchar(40), @srch3 nvarchar(40),  
    SET @srch1 = '%this%'
    SET @srch2 = '%that%'
    SET @srch3 = '%the other%'

SELECT * FROM DataTable dt
    WHERE  CONCAT(dt.field1, dt.field2) LIKE @srch1 
        OR CONCAT(dt.field1, dt.field2) LIKE @srch2 
        OR CONCAT(dt.field1, dt.field2) LIKE @srch3

Thank you!

Upvotes: 0

Views: 401

Answers (1)

EricZ
EricZ

Reputation: 6205

How about this?

DECLARE @srch TABLE (srch_field nvarchar(40))

INSERT INTO @srch VALUES ( '%this%'), ('%that%') ,('%the other%')

SELECT * FROM DataTable dt
WHERE  EXISTS (
  SELECT NULL FROM @srch s WHERE CONCAT(dt.field1, dt.field2) LIKE srch_field
)

Upvotes: 1

Related Questions