CODEWITHSUNDEEP
sqloracle-database
Teleporting Goat
Teleporting Goat

Reputation: 467

Use like '%' and match NULL values with NUMBER columns

This question is almost exactly like mine but none of the answers work with my case.

If it was my question I'd slightly edit it to make it a different question. This question is thus different from the linked one.

Here's the problem: I want a way to match any non-null value ('%') AND null values.

The thing is:

None of the answers apply to this problem because you can't make a null into an empty string when the column is a NUMBER.

How could I do to achieve this?

Some context:

My procedure takes a lot of parameters, and does a select with all of them. They can all have a value or be null, so if they're null they're replaced with '%'.

That way, the procedure does :

where t.col1 like param1
and t.col2 like param2
...

Most of the times, only one or two parameters is not null. For the others parameters, the procedure needs to match on every row.

But when the value is null, like '%' doesn't match the row. I'm looking for a way to match anything when param x is empty (so paramx = '%')

Upvotes: 1

Views: 1287

Answers (5)

Marmite Bomber
Marmite Bomber

Reputation: 21085

The proposals from other answers based on OR or NVL/ COALESCE are "elegant" and simple, but as a rule they inhibit the index access, which is the most important thing.

You may step down to use dynamic SQL to address the optional parameter problem - which is your case. If a parameter is not passed (it is NULL) - simple ignore it.

So for example with two parameters, if both parameters are passed, generate following SQL

select * from tab t
where t.col1 like :param1
and t.col2 like :param2

If only parameter 1 is given, generate this SQL:

select * from tab t
where t.col1 like :param1

With no parameter you will end with

select * from tab t

Technically it is preferable to have in all SQL statements the same number of bind variables, which is not the case in the above proposal. See this answer for detailed explanation of the trick popularized by Tom Kyte to preserve the number of bind variable with optional parameters.

For example the second statement with only parameter 1 would yield following SQL

select * from tab t
where t.col1 like :param1
and (1=1 or t.col2 like :param2)

The shortcut logik of 1=1(which is TRUE) eliminates the second part of the predicate, but the bind variable is still used, so the number of the bind variables remains constant.

The big advantage is a fine index range access of this query

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191415

My procedure takes a lot of parameters, and does a select with all of them. They can all have a value or be null, so if they're null they're replaced with '%'.

That seems like you're making life hard for yourself. Leave them null, then do:

where (param1 is null or t.col1 like param1)
and (param2 is null or t.col2 like param2)

If param1 (the procedure argument; life is simpler when your parameter/variable names and column names are different... so I've changed the column names to make it a bit clearer) is null it is basically ignored* and all rows pass that part of the filter, whether the column value is null or not null. If param2 is not null then the is null check for that fails and only rows with (not-null) column values that match param2 value meet that part of the filter.

* Conditions in an or can be evaluated in any order; putting the is null check first doesn't necessarily mean the like won't be evaluated - but the optimiser is pretty smart about that sort of thing

Upvotes: 4

HereGoes
HereGoes

Reputation: 1320

In Oracle NVL can be used instead of ISNULL

IF NVL(aNumberColumn,-1) = -1 THEN
    ---whatever
END IF;

Upvotes: 1

Radagast81
Radagast81

Reputation: 3016

Oracle automatically converts NUMBER to VARCHAR2 for like-conditions. So what you have todo is do that yourself so you can use coalesce:

COALESCE(TO_CHAR(column), ' ') like '%'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

If you want to match a specific value and NULL, you can use OR:

where col = <specific value> or col is null

Upvotes: 1

Related Questions