remove null values appended to data

[harryhome]

Hi

I am getting input data in dataset which in turn go created from db2 with values appened with null.
looks like "ABC "
Right now using trim funcation not able to remove those. length of my data is varying otherwise i can use substring.
let me know how to remove null appended to data.

[pandeesh]

What's the output you are getting if you use trim().
Are you sure that's NULL or WHITESPACE?
If it's WHITESPACE please try with Convert() or Stripwhitespaces().

Code: Select all

Convert(" ","",inputcolumn)

[harryhome]

What's the output you are getting if you use trim().
Are you sure that's NULL or WHITESPACE?
If it's WHITESPACE please try with Convert() or Stripwhitespaces().

Code: Select all

Convert(" ","",inputcolumn)

Those are not whitespace there are null values. also if apply trim function then still null values are there.

[pandeesh]

Sorry harry!I am not at all able to understand how a null value will be appended to data.

For investigating can you please post the results of below?

Len("ABC")---without null

Len("ABC ")--- with null values appended as per your question

I guess the results should be same if they are really null.

If the results vary ,then that will not be null.(if 3 is the result for first one and 5 is the result for second one )

Someone please correct me if my understanding is in correct

Thanks

[harryhome]

with
Convert("ABC "," ","") getting blank output as " "

StripWhiteSpace("ABC ") getting "ABC "

LEN("ABC ") getting 4

not sure what is this coming from db2. so special character it seems

[harryhome]

with
Convert("ABC "," ","") getting blank output as " "

StripWhiteSpace("ABC ") getting "ABC "

LEN("ABC ") getting 4

not sure what is this coming from db2. so special character it seems

[chulett]

You need to determine the hex value of these characters. Be it "nulls" or otherwise, they will have a value and only by knowing what it is can you deal with it properly.

[qt_ky]

I thought null appended to anything results in null.

Are you saying you don't know the source of the data or is the source db2?

[harryhome]

Source is db2. If I extract data to sequential file and then open that with Notepad++ it give me "ABCnullnullnull"

[ray.wurlod]

In server jobs and BASIC Transformer stages there is the concept of a "null string", represented by the system variable @NULL.STR. It would seem that you have these in your data.

Have you tried trimming Char(128) from your string? That's the DataStage internal representation of null.

Otherwise, consider using a BASIC Transformer stage or a server job to trim @NULL.STR from the data.

[BI-RMA]

Source is db2. If I extract data to sequential file and then open that with Notepad++ it give me "ABCnullnullnull"

Hi harryhome,

I guess your source-datatype in db2 is char. Use the db2-trim-function and set the column to VARCHAR in DataStage. This should return "ABC" with a length of 3 bytes.

[prakashdasika]

I am pretty sure it cannot be null. It must be some unprintable character, the best way as suggested by Craig is determine the Hex value and get the equivalent ASCII value and use Convert Function to replace it.

[BI-RMA]

I am pretty sure it cannot be null.

So why are You so sure? We are running DB2 for iSeries and the database does right-pad strings of type char with binary NULL values - hex(00). Definitely!

I do not know exactly how DB2 UDB will behave, but since we do not know exactly which version is used we are left with guesses, I am afraid.

[ray.wurlod]

NUL (0x00) is not the same as NULL (unknown value). It's unfortunate that the nomenclature was chosen as it was.

[yabhinav]

I had the same issue and here is how I solved it

1st way:
In transformer stage use the type conversion UStringToString on your column. It will remove the appended NULL values

2nd way is to do it using a Modify stage. In the specification use the below syntax:
new_column_name:string = string_trim[NULL, end](old_column_name)