Page 1 of 1
Like operator with null handling
Posted: Wed Oct 05, 2011 1:00 am
by kumar444
Code: Select all
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.
Posted: Wed Oct 05, 2011 1:37 am
by vishal_rastogi
i think you can use the combination of isnotnull and len(trim(column))<>0
Posted: Wed Oct 05, 2011 1:59 am
by SURA
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
Posted: Wed Oct 05, 2011 2:52 am
by ray.wurlod
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')
Posted: Wed Oct 05, 2011 3:46 am
by BI-RMA
The only question left is: why compare the result of the Index-function to a string (should be int32).
Posted: Wed Oct 05, 2011 3:18 pm
by ray.wurlod
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))