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
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%'
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%'
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.
'somewhere' || 'wolfenstein' like '%wherewolf%'returns true.