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

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   '%')
        )

CodePudding user response：

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.

CodePudding user response：

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.