CODEWITHSUNDEEP
sqlsql-server
Young Pattewa
Young Pattewa

Reputation: 87

Using LIKE for multiple columns SQL Server

I am using SQL Server to search through multiple columns (over 20 in reality) in my table containing part of a certain value, but I keep getting the error

'CONCAT' is not a recognized built-in function name

I am running SQL Server 2017 and the function should be available, so I'm not sure if there's an error in my query or if something else is causing the error.

SELECT *
FROM table 
WHERE 
   CONCAT(col1, col2, col3, col4, col5, col6, col7, col8) LIKE '%val%'

Thank you in advance

Upvotes: 1

Views: 4109

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81950

Just another option, however I suspect concat() or Gordon's (+1) would be more performant

Select *
 From  YourTable A
 Where (select A.* for xml raw) like '%YourSearch%'

EDIT

Just for fun, I ran a comparison of the the concat() vs the xml approach. The sample was 25K rows with 101 columns. The average of 5 runs for concat was 886ms while the xml was 1.162.

Clearly concat() is the winner, but the xml approach was not horrible, and may be another option in a discovery phase.

Upvotes: 3

amcdermott
amcdermott

Reputation: 1585

I'd tweak Gordon's solution slightly to insert some delimiting character between the concatenated terms to avoid scenarios where the end of the one value and the start of the next combine to give you a match.

where (col1 + '|' + col2 + '|' + col3 + '|' + col4 + '|' + col5 + '|' + col6 + '|' + col7 + '|' + col8) like '%val%'

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Use +:

where (col1 + col2 + col3 + col4 + col5 + col6 + col7 + col8) like '%val%'

This assumes that all the columns are non-NULL string columns.

If they could be NULL or are not strings, then you need explicit conversion:

where (coalesce(convert(nvarchar(max), col1), '') +
       coalesce(convert(nvarchar(max), col2), '') +
       . . .
       coalesce(convert(varchar(max), col8), '') +
      ) like '%val%'

Upvotes: 4

Related Questions