CODEWITHSUNDEEP
sqlsql-serverwildcard
Luca S.
Luca S.

Reputation: 63

SQL wildcard not producing any results

I guess there should be a rather simple explanation for this, but I can't come up with any idea.

This query works as expected (result = 1 row):

SELECT Sites.Master_ID AS Master_ID, Scopes.Name AS Scope, Brands.Extension AS [Brand], Sites.ID AS [Site ID], Sites.Name AS [Site Name], Sites.Address, Sites.CAP, Sites.City, Countries.Name AS Country
FROM Sites
INNER JOIN Scopes ON Sites.scope_ID = Scopes.ID INNER JOIN Brands ON Sites.brand_ID = Brands.ID
INNER JOIN Countries ON Sites.country_ID = Countries.ID
WHERE 'BA001' IN (Scopes.Name, Brands.Extension, Sites.ID, Sites.Name, CONVERT(nvarchar(MAX),Sites.Address), Sites.CAP, Sites.City, Countries.Name)

While adding the wildcard it's not (result = 0 row):

SELECT Sites.Master_ID AS Master_ID, Scopes.Name AS Scope, Brands.Extension AS [Brand], Sites.ID AS [Site ID], Sites.Name AS [Site Name], Sites.Address, Sites.CAP, Sites.City, Countries.Name AS Country
FROM Sites
INNER JOIN Scopes ON Sites.scope_ID = Scopes.ID INNER JOIN Brands ON Sites.brand_ID = Brands.ID
INNER JOIN Countries ON Sites.country_ID = Countries.ID
WHERE '%BA001%' IN (Scopes.Name, Brands.Extension, Sites.ID, Sites.Name, CONVERT(nvarchar(MAX),Sites.Address), Sites.CAP, Sites.City, Countries.Name)

Am I using the wildcards the proper way?

Looking forward to your reply. Many thanks.

PS: I'm launching the query from SQL Server Management Studio 18.4

Upvotes: 0

Views: 345

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

Wildcards work with LIKE, not IN.

You could concatenate the values together:

WHERE CONCAT(Scopes.Name, Brands.Extension, Sites.ID, Sites.Name, CONVERT(nvarchar(MAX),Sites.Address), Sites.CAP, Sites.City, Countries.Name) LIKE '%BA001%'

You might want to include delimiters:

WHERE CONCAT_WS('|', Scopes.Name, Brands.Extension, Sites.ID, Sites.Name, CONVERT(nvarchar(MAX),Sites.Address), Sites.CAP, Sites.City, Countries.Name) LIKE '%BA001%'

CONCAT_WS() was introduced in SQL Server 2017.

Upvotes: 1

Related Questions