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

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