remove null values appended to data
Moderators: chulett, rschirm, roy
remove null values appended to data
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.
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.
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().
Are you sure that's NULL or WHITESPACE?
If it's WHITESPACE please try with Convert() or Stripwhitespaces().
Code: Select all
Convert(" ","",inputcolumn)
pandeeswaran
Those are not whitespace there are null values. also if apply trim function then still null values are there.pandeesh wrote: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)
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
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
pandeeswaran
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Hi harryhome,harryhome wrote:Source is db2. If I extract data to sequential file and then open that with Notepad++ it give me "ABCnullnullnull"
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.
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon
-
- Premium Member
- Posts: 72
- Joined: Mon Jul 06, 2009 9:34 pm
- Location: Sydney
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!prakashdasika wrote:I am pretty sure it cannot be null.
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.
![Smile :)](./images/smilies/icon_smile.gif)
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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)
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)