Remove Zero Rows from a String COlumn

[Magesh_bala]

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....

[pandeesh]

try this:

Code: Select all

if trim(input)='0' then SPACE(1) else input

[pandeesh]

i have misunderstood the requirement

Have a stage variable

Code: Select all

if AsInteger(input)+0=0 then 1 else 0

I believe even AsInteger() also not required.

In the transformer set the constraint as StageVar=0

so that non zero strings will be passed

[pandeesh]

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

[zulfi123786]

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

Code: Select all

If Not(Isvalid(int32,input)) then SetNull() Else If AsInteger(input)=0 then ' ' Else input

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'

[chulett]

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:

Code: Select all

Convert('123456789','',YourField) <> YourField

Use svIsValid as your constraint.

[pandeesh]

Awesome Craig!