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

Question

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.

@Habib WHERE name || surname LIKE '%john%' is a very, very, very bad solution both for MySQL and SQL Server, as the server can't use of any indexes to accelerate the query. If you have a table with 50K rows, the server will have to search every single one of them. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 20, 2020 at 14:43
@Habib in both databases, the real solution for substring searches is to create a full-text search index and use it. It's not as easy as the "solution" but it's orders of magnitude faster. In your case though you don't need any tricks as you aren't searching substrings — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 20, 2020 at 14:44
BTW out of all the answers in that question, the one you picked is simply wrong and can't handle NULLs in MySQL either — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 20, 2020 at 14:45

Tim Biegeleisen · Accepted Answer · 2020-10-20 14:43:44Z

5

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,%';

answered Oct 20, 2020 at 14:43

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-10-20 14:52:25Z

2

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.

answered Oct 20, 2020 at 14:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

Habib Over a year ago

Is there any advantage of using comparison separately? I don't see any disadvantage of using the comparison by where in clause and the advantage in my opinion is that it will reduce LOC for me.

Tim Biegeleisen Over a year ago

@Habib One issue here is that of making your WHERE clause sargable, which means making it possible for the database to use an index. When you try to search for a matching name inside a concatenated string using wildcards, immediately an index cannot be used. So it is better to try to compare with equality using separate terms in the WHERE clause, if possible.

Habib Over a year ago

@TimBiegeleisen Thank you for pointing the issue. Using separate terms isn't an issue and I will definitely use it separate after this comment of your. Currently is I don't need to use anything using the query just trying to find out if the string exists.

SnnG · Accepted Answer · 2020-10-20 14:43:31Z

0

For MS SQL this may work;

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

answered Oct 20, 2020 at 14:43

SnnG

615 bronze badges

1 Comment

Habib Over a year ago

I think you didn't visited the link above it's already mentioned in that link. Still thanks.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related