Like operator with null handling

kumar444 · Post by **kumar444** » Wed Oct 05, 2011 1:00 am

If (Column_1)='0004400' And (column_2)='1221' And Index(column_3,"NOG",1) <>'0' Then 1 Else 0 

Output:

column1       column2       column3
0004400       1221             DONOG
0004400       1221             NOG-NEW

Eliminate all other cases

Hi I have a requirement where i need to reject null values and empty spaces as well for all the three columns shown in the code.
The Index function here indicates that if a substring "NOG" exists then output the row.

I really appreciate your help.

vishal_rastogi · Post by **vishal_rastogi** » Wed Oct 05, 2011 1:37 am

i think you can use the combination of isnotnull and len(trim(column))<>0

SURA · Post by **SURA** » Wed Oct 05, 2011 1:59 am

vishal_rastogi wrote:i think you can use the combination of isnotnull and len(trim(column))<>0

len(trim(caol)) <> '' is right for char and not sure about 0.

DS User

ray.wurlod · Post by **ray.wurlod** » Wed Oct 05, 2011 2:52 am

You don't need If expr Then 1 Else 0 because, in a Boolean context, expr returns 1 (True) or 0 (False). Use any technique you like to handle null.

Code: Select all

(NullToEmpty(Column_1)='0004400') And (NullToEmpty(column_2)='1221') And (Index(NullToEmpty(column_3),"NOG",1) <>'0')

BI-RMA · Post by **BI-RMA** » Wed Oct 05, 2011 3:46 am

The only question left is: why compare the result of the Index-function to a string (should be int32).

ray.wurlod · Post by **ray.wurlod** » Wed Oct 05, 2011 3:18 pm

Good point. The joys of copy-and-paste. It will work as a string, but require an unnecessary data type conversion. Better is:

Code: Select all

(NullToEmpty(Column_1)='0004400') And (NullToEmpty(column_2)='1221') And (Index(NullToEmpty(column_3),"NOG",1) <> 0)

And even better is:

Code: Select all

(NullToEmpty(Column_1)='0004400') And (NullToEmpty(column_2)='1221') And (Index(NullToEmpty(column_3),"NOG",1))