Remove Zero Rows from a String COlumn
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 86
- Joined: Mon Nov 27, 2006 3:42 am
- Location: Wilmington
Remove Zero Rows from a String COlumn
Hi All,
I have a scenario where I need to replaces all the rows with Zero into Single space...
Invalid Rows
Eg:
0
00
000
0000
The column is defined as Varchar(20) so it can have any combination of Zero values starting from 1 to 20 zeros
I need to remove all the rows where it has Zero's as defined above. It can be of any combination.
Your help will be really appreciated.
Valid Rows
-------------
1010
0001
1000
I dont want to make any changes to the valid Rows....
I have a scenario where I need to replaces all the rows with Zero into Single space...
Invalid Rows
Eg:
0
00
000
0000
The column is defined as Varchar(20) so it can have any combination of Zero values starting from 1 to 20 zeros
I need to remove all the rows where it has Zero's as defined above. It can be of any combination.
Your help will be really appreciated.
Valid Rows
-------------
1010
0001
1000
I dont want to make any changes to the valid Rows....
i have misunderstood the requirement
Have a stage variable
I believe even AsInteger() also not required.
In the transformer set the constraint as StageVar=0
so that non zero strings will be passed
Have a stage variable
Code: Select all
if AsInteger(input)+0=0 then 1 else 0
In the transformer set the constraint as StageVar=0
so that non zero strings will be passed
pandeeswaran
If you need to retain the zero strings with a space then,
Code: Select all
if AsInteger(Input)+0=0 then SPACE(1) else input
pandeeswaran
-
- Premium Member
- Posts: 730
- Joined: Tue Nov 04, 2008 10:14 am
- Location: Bangalore
As the colum is defined to be varchar its always a good practise to check before type conversion using IsValid() so the above posted code with slight modification would look like
in the above code there are two assumptions:
1. if its not a valid integer then Null has to be populated
2. the input column is of the sql type 'integer'
Code: Select all
If Not(Isvalid(int32,input)) then SetNull() Else If AsInteger(input)=0 then ' ' Else input
1. if its not a valid integer then Null has to be populated
2. the input column is of the sql type 'integer'
- Zulfi
There's no conversion of any kind here, they just need a simple check for all zeroes so those rows can be filtered out. Stage variable svIsValid:
Use svIsValid as your constraint.
Code: Select all
Convert('123456789','',YourField) <> YourField
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers