CODEWITHSUNDEEP
sql-servert-sql
MisterniceGuy
MisterniceGuy

Reputation: 1786

SQL IN Wildcard Char

I have a query which uses a IN Filter and works fine. I am wondering if there is something like a wildcard char which will not filter anything

Select * 
FROM [tbl_Leads]
where p_contact_first_name in ('Tom')

the above works as desired but what happens if i don't want to filter by anything and return all. I know i can create a second query and removing the IN clause but from the logic if possible it would be nicer if i can check for existence of filter value and if none present replace it with wildcard char

Upvotes: 0

Views: 105

Answers (4)

EzLo
EzLo

Reputation: 14189

The IN operator doesn't allow wildcards or partial values to match. In fact it's just a syntactic sugar of a chaining of OR logical operators.

This query:

SELECT 1 FROM SomeTable AS T
WHERE T.Column IN (1, 2, 3, 4)

Is exactly the same as:

SELECT 1 FROM SomeTable AS T
WHERE 
    T.Column = 1 OR
    T.Column = 2 OR
    T.Column = 3 OR
    T.COlumn = 4

And this is why having a NULL value with a NOT IN list will make all the logic result be UNKNOWN (hence interpreted as false and never return any record):

SELECT 1 FROM SomeTable AS T
WHERE T.Column NOT IN (1, 2, NULL, 4)

Will be:

SELECT 1 FROM SomeTable AS T
WHERE 
    NOT(
        T.Column = 1 OR
        T.Column = 2 OR
        T.Column = NULL OR      -- Always resolve to UNKNOWN (handled as false for the whole condition)
        T.COlumn = 4
        )

You have a few options to conditionally apply a filter like IN:

  • Use OR against another condition:

    DECLARE @ApplyInFilter BIT = 0

SELECT 1 FROM SomeTable AS T
WHERE 
    (@ApplyInFilter = 1 AND T.Column IN (1, 2, 3, 4)) OR
    @ApplyInFilter = 0

  • Avoid the query altogether (have to repeat whole statement):

    DECLARE @ApplyInFilter BIT = 0

IF @ApplyInFilter = 1
BEGIN
    SELECT 1 FROM SomeTable AS T
    WHERE 
        T.Column IN (1, 2, 3, 4)
END
ELSE
BEGIN
    SELECT 1 FROM SomeTable AS T
END

  • Use Dynamic SQL to conditionally omit the filter:

    DECLARE @ApplyInFilter BIT = 0

DECLARE @DynamicSQL VARCHAR(MAX) = '
    SELECT 1 FROM SomeTable AS T '

IF @ApplyInFilter = 1
    SET @DynamicSQL += ' WHERE T.Column IN (1, 2, 3, 4) '

EXEC (@DynamicSQL)

Unfortunately, the best approach if you plan to have multiple conditional filters is the Dynamic SQL one. It will be the hardest to code but best for performance (with some caveats). Please read George's Menoutis link to fully understand pros and cons of each approach.

Upvotes: 1

Alexey Chuksin
Alexey Chuksin

Reputation: 90

Make two requests. The performance of these two queries will be better than that of a single universal query. You can compare the execution plan for these queries.

Upvotes: 0

George Menoutis
George Menoutis

Reputation: 7240

As said by Dale Burrell, the fast way to implement dynamic search conditions (exactly what your problem is) is to put code like:

....and field=values or @searchThisField=0

The other solution would be dynamic sql.

I consider Erland Sommarskog's article to be the epitome of analyzing this specific subject.

Upvotes: 0

Avi
Avi

Reputation: 1845

You can make use of not exists to get the desired results. From my understanding if you have a name like Tom you want only that row and if it does not you want all other rows to be displayed. 

 select 1 as ID, 'Tom' as Name into #temp 
 union all 
 select 2 as ID, 'Ben' as Name union all 
 select 3 as ID, 'Kim' as Name 
 union all 
 select 4 as ID, 'Jim' as Name  

 This query will check if Tom exists then display only that row if not display all. 

 select * from #temp   
 where name = 'TOm'  or not exists (select 1 from #temp where name = 'Tom') 

 Result from above query: 

   ID Name 
   1  Tom

Lets test it, by deleting the row where Tom record is. 

Delete from #temp 
where name = 'Tom'

If you run the same query you get the following result. 

select * from #temp 
where name = 'TOm' or not exists (select 1 from #temp where name = 'Tom') 

 ID Name
 2  Ben
 3  Kim
 4  Jim

Upvotes: 0

Related Questions