Replace blank('') as NULL in Stored Procedure

General
Enthusiast

Replace blank('') as NULL in Stored Procedure

Hi,

     In SP if parameter has value as blank(''), then i have to change tat as NULL. Please help me.

7 REPLIES
Junior Contributor

Re: Replace blank('') as NULL in Stored Procedure

You can use the basic SQL NULLIF(parameter, '')

Dieter

Enthusiast

Re: Replace blank('') as NULL in Stored Procedure

Hi Dieter,
Thanks for ur response..
The below query is working if 'Column1' is not null and parameter is blank('')..
Select * from table where COALESCE((Nullif(Parameter,'')), TRIM(Column1)) LIKE '%'|| TRIM(Column1) || '%'
If 'Column1' is null and prameter is blank('') its not working. Can u plz help me
Enthusiast

Re: Replace blank('') as NULL in Stored Procedure

I think is not linked with the blank parameter, it is related with the value in Column1.

When the column 1 is null, then the comparison with like is a little complex. 

Can you please paste the error you are facing, or output when column1 is null?

Khurram
Junior Contributor

Re: Replace blank('') as NULL in Stored Procedure

When Column1 is NULL your calculation is: WHERE NULL LIKE NULL and this is NOT TRUE.

What do you want as result when one or both are NULLs?

Dieter

Enthusiast

Re: Replace blank('') as NULL in Stored Procedure

For example I have 10records having column1 value  as NULL.

Select * from table where column1 IS NULL -- It'l return 10 rows

In my above query, the parameter value may be 'AAA'/blank('').. If its value based on that it has to return rows..in my case no rows will return...If it's  blank(''),it has to return 10 rows.....

If column1 is null, it didnt return any row

Junior Contributor

Re: Replace blank('') as NULL in Stored Procedure

Hi Selvi,

if "Parameter" is NULL or '' then return NULL rows else do the LIKE comparison?

WHERE (Parameter LIKE '%'|| TRIM(Column1) || '%')
OR (NULLIF(Parameter, '') IS NULL AND Column1 IS NULL)

Dieter

Enthusiast

Re: Replace blank('') as NULL in Stored Procedure

Thanks Dieter!!