CODEWITHSUNDEEP
sqlsql-serverstringsql-like
Habib
Habib

Reputation: 41

Check if string is found in one of multiple columns in SQL

I want to search a string in multiple columns to check if it exists in any. I found a solution for it here
The answer by Thorsten is short but that is a solution for mysql server not for SQL Server. So I would like to apply similar query in SQL Server.
Here is the query suggested by Thorsten.

Select * 
from tblClients 
WHERE name || surname LIKE '%john%'

I tried it as

/* This returns nothing */
Select * 
from Items 
Where ISNULL(Code, '') + ISNULL(Code1, '') = '6922896068701';
Go

/* This generate error Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '|'. 
I also used this one in mysql but it does not show the exact match.
*/
Select * 
from Items 
WHERE Code || Code1 = '6922896068701';
Go

/* This generate error Msg 4145, Level 15, State 1, Line 5
An expression of non-boolean type specified in a context where a condition is expected, near 'Or'. */
Select * 
from Items 
WHERE Code Or Code1 = '6922896068701';
Go

Is it really possible in SQL Server?
Note: The answer by J__ works accurately in the upper Question link but I want the comparison string to be entered once for all columns where I look for it like Thorsten.

Upvotes: 0

Views: 1947

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

I would recommend doing the two comparisons separately:

WHERE name LIKE '%john%' OR
      surname LIKE '%john%'

Unless you specifically want to find times when the names are combined, such as "Maryjoh" "Needlebaum" or whatever.

It is generally better to focus on one column at a time, because that helps the optimizer.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

Actually I think that separate logical checks in the WHERE clause for each column is the way to go here. If you can't do that for some reason, consider using a WHERE IN (...) clause:

SELECT *
FROM Items
WHERE '6922896068701' IN (Code, Code1);

If instead you want LIKE logic, then it gets tricky. If you knew that the matching codes would always consist of numbers/letters, then you could try:

SELECT *
FROM Items
WHERE ',' + Code + ',' + Code1 + ',' LIKE '%,6922896068701,%';

Upvotes: 5

SnnG
SnnG

Reputation: 61

For MS SQL this may work;

 Select * 
 from Items 
 WHERE Code = '6922896068701' Or Code1 = '6922896068701'

Upvotes: 0

Related Questions