CODEWITHSUNDEEP
sql-serversql-server-2008
Madam Zu Zu
Madam Zu Zu

Reputation: 6605

SQL Server WHERE with wildcard

Is it possible to use a wildcard for the where in statement in SQL Server 2008?

For example, I currently have:

SELECT something  
FROM myTable  
WHERE    (ORG + '-' + ORGSUB like '5015001-________' or
          ORG + '-' + ORGSUB like '5015018-________' or 
          ORG + '-' + ORGSUB like '_______-________')

I need to do it this way:

SELECT something  
FROM myTable 
WHERE
   (ORG + '-' + ORGSUB) in( '5015001-________','5015018-________','_______-________')

i'm going to be passing those values through a stored procedure as a comma delimited list. is there another way to get it done?

Upvotes: 0

Views: 263

Answers (4)

anon
anon

Reputation:

Why a comma-separated list?

DECLARE TYPE dbo.OrgSub AS TABLE(s VARCHAR(32));
GO
CREATE PROCEDURE dbo.SearchOrgSubs
  @OrgSub dbo.OrgSub READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT t.something 
    FROM dbo.mytable AS t
    INNER JOIN @OrgSub AS o
    ON t.ORG + '-' + t.ORGSUB = o.s;
END
GO

Now you can pass the set into the stored procedure from C# or wherever, without first having to form it into a comma-separated list.

Upvotes: 1

Muhammad Hani
Muhammad Hani

Reputation: 8664

You can create a temp Table contains the result of a split function.

SELECT somthing 
from myTable  
    JOIN dbo.Split('5015001-________','5015018-________','_______-________') as Splits 
    on (ORG + '-' + ORGSUB) like Splits.items

Upvotes: 0

Peter Gluck
Peter Gluck

Reputation: 8236

Why do you even care about ORGSUB in your query (as provided in the example)? Seems to me you should rewrite your WHERE clause to look for the components separately, e.g.:

SELECT something  
FROM myTable  
WHERE ORG IN (5015001, 5015018, ...) 
[add other criteria as appropriate]

Upvotes: 1

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

Take your comma delimited list, split it, and insert it into a temp table...

You can then use a LIKE statement in a JOIN to this temp table:

SELECT something
FROM myTable mt
JOIN #tempTable tt
    ON mt.ORG + '-' + mt.ORGSUB LIKE tt.SearchValue

Upvotes: 1

Related Questions