SQL - Conditional LIKE expression based on query parameter

Question

I want to select rows using LIKE %:myParam% based on the query parameter ONLY if it is not null or empty. If the query parameter is null or empty, I want to select all records & drop the Like %%. How can I do that? I have tried with case-when but I am not sure How to use Like in it. Basically I want to use the like based on null check's result.

I have something like this:

Select * from myTable tbl
WHERE tbl.myCol LIKE '%' || :myParam|| '%'

For example: Select * from myTable returns 10 rows - from which 4 rows have myCol= null, I want to select 10 rows if :myParam is null/empty otherwise I want to get rows matching the LIKE expression. I dont want to get 6 rows if null is passed as :myParam

Answer 1

I believe that the expression LIKE '%%' will return all records (at least it does in MySQL), so you can use COALESCE() to change a NULL value into empty string:

SELECT *
FROM myTable tbl
WHERE tbl.myCol LIKE '%' || COALESECE(:myParam, '') || '%'

The nice thing about this approach is that it avoids a messy CASE expression, or something possibly worse than that.

Answer 2

Your original query should work. Just check if you are passing space(s) in :myParam.

Select * from myTable tbl
WHERE tbl.myCol LIKE '%' || :myParam|| '%'

For example if I run the following query it returns me the list of all tables.

SELECT table_name FROM user_tables
WHERE table_name like '%' || '' || '%'

And the following query returns the list of all tables containing the word TEMP.

SELECT table_name FROM user_tables
WHERE table_name like '%' || trim('TEMP') || '%'

You can try putting a trim around your myParam.

Select * from myTable tbl
WHERE tbl.myCol LIKE '%' || trim(:myParam) || '%'

Try this query for including rows where your column contains NULL values.

SELECT * from myTable tbl
WHERE (tbl.myCol LIKE '%' || :myParam|| '%' 
OR (TRIM(:myParam) IS NULL AND tbl.myCol IS NULL))