How to retrieve NULL field values from the table using LIKE '%'

[request]

I have a stored procedure which has 4 input parameters.

An input parameter may not have a value.

I have the WHERE clause coded as follows:

where field1 like 'p1'||'%'
and field2 like 'p2'||'%'
and field3 like 'p3'||'%'
and field4 like 'p4'||'%'

Where p1,p2,p3,p4 are the input parameters.

Now, if one input parameter p1 value is NULL, then it does not retrieve records from the table where field1 value is null since like '%' does not retrieve NULL values.

How can I modify my above query so that even if the table has like '%', it will retrieve the records from the table where the field values are NULL.

Please help. Thanks.

 

[lewisp]

[tt]where Nvl(field1,'x') like 'p1'||'%'
and Nvl(field2,'x') like 'p2'||'%'
and Nvl(field3,'x') like 'p3'||'%'
and Nvl(field4,'x') like 'p4'||'%'[/tt]

will return rows with null values in the columns, but only when no parameter value is entered.

Also, this query will not use indexes. There may be a more efficient way of doing this, however.

 

[SantaMufasa]

...plus, I believe you probably do not want the quotes around your 'p1'...'p4' values; you are not looking for literals, you are looking for incoming parameter values that are stored in those arguments, right?