2

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

Improve this question
1
  • tbl.myCol LIKE '%' || nvl(:myParam,tbl.myCol) || '%' Commented Jun 16, 2016 at 3:32

2 Answers 2

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Using the above query filters the null items also and returns only non-null column rows. I want to get null rows also if :myParam is null/empty else I want to filter based on :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. Using your query returns 6 rows if I pass null in :myParam.
I think you have something else inside your :myParam. My query should be working for you AFAIK.
I am not getting the rows which have null in myCol if I supply :myParam=null . I have rechecked it & still getting only non-null myCol rows. I am using Oracle Database
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))
Improve this answer

6 Comments

The above queries exclude the rows which have null in myCol when null is supplied as a parameter. I want to select all rows if :myParam=null, else if :myParam is not null then I want to select rows based on LIKE epression.
@zeppelin This answer looks correct to me, perhaps there is a problem with your query/data.
@TimBiegeleisen Please read my comments on your answer. I have added an example there to explain it better.
@zeppelin Added one more query in my original answer.
@zeppelin If his last query suddenly starts working, then it means you had whitespace in your :myParam parameter.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.