Sql where clause, Get All if value is empty or null else use like statement

Question

I have a column

-Category

Red Blue Yellow

I would like to render a conditional statement. If Title is null, then get all, else title like @Title.

  SELECT [Category]
  FROM [dbo].[Records]
  WHERE 
         CASE WHEN @SearchText IS NULL 
                        THEN title = title 
                        ELSE (Title like '%' + @SearchText + '%') 
         END

The results should look like this

if @Title is null or empty

return all values

Red Blue Yellow

Else where title like '%red%' return red

This is the best I could ome up with

    and (
            (@SearchText IS NULL and title = title)
          or
          (@SearchText IS not NULL and Title like '%' + @SearchText + '%')
        )

I would suggest you use Dynamic SQL to run separate queries depending on the value of `title`. Or youe app can just run two separate queries using an `if` block. — The Impaler, Oct 17 '21 at 15:25
@TheImpaler I plan on doing this with 8 different columns that's almost 100 different query combinations. — Train, Oct 17 '21 at 15:29
Does this answer your question? [How do I create a stored procedure that will optionally search columns?](https://stackoverflow.com/questions/205526/how-do-i-create-a-stored-procedure-that-will-optionally-search-columns) — Serg, Oct 17 '21 at 16:07

Simone Urbani · Answer 1 · 2021-10-17T16:02:57.510

4

Try something like this:

SELECT [Category]
FROM [dbo].[Records]
WHERE ISNULL(@SearchText, '') = '' OR Title like '%' + @SearchText + '%'

but I think

SELECT [Category]
FROM [dbo].[Records]
WHERE Title like CONCAT('%', @SearchText, '%')

probably works.

See also How does SQL Server evaluate logical expressions? in order to understand logic operator evaluation in sql server.

edited Oct 17 '21 at 16:02

answered Oct 17 '21 at 15:46

Simone Urbani

130
1
5

K V Prashant · Accepted Answer · 2021-10-17T16:06:40.777

2

Your code can be updated to this:

SELECT [Category]
FROM [dbo].[Records]
WHERE (@SearchText IS NULL OR (Title like '%' + ISNULL( @SearchText ,'')+ '%'))

If you feed null then first condition will be true else second.

edited Oct 17 '21 at 16:06

answered Oct 17 '21 at 15:38

K V Prashant

77
4

Doesn't the or condition run the like statement (Title like '%' + @SearchText + '%') ? I don't want to execute the like statement if i'ts null. The like statment doesn't use indexes and will scan the entire table. – Train Oct 17 '21 at 15:42
it will check the first condition and if its true then will not go to next condition, its similar to how you use in any other language – K V Prashant Oct 17 '21 at 16:02
@KVPrashant: that statement isn't entirely correct - SQL Server **DOES NOT** do boolean evaluation strictly as the statement is written; it is quite possible the `LIKE` will be evaluated first. T-SQL is **NOT** like a "deterministic" programming language like C# - there's **no guarantee** for this behavior – marc_s Oct 17 '21 at 17:24
1

@marc_s I just gave a thought with out any background search, thanks for correcting. if you don't want to query to check the second statement at all you might try write separate query : if SearchText is null (just select all) else (Select with that like filter) – K V Prashant Oct 17 '21 at 17:41
What @marc_s is trying to say is that you can't guarantee the order of evaluation, but that does not change the actual results, because ultimately both sides of the `OR` are evaluated. The only time this usually makes a difference is when there is a subquery involved, or in cases of exceptions such as overflow. – Charlieface Oct 17 '21 at 19:39

Sql where clause, Get All if value is empty or null else use like statement

2 Answers2