Length(Varchar) in db2
Moderators: chulett, rschirm, roy
Length(Varchar) in db2
Hi All,
I have a problem with the QA guys.
select min(length(addr1)) from schema.tablename
gives 30. where addr1 is of varchar(30).
I think I have faced this issue even earlier. The length() in db2 gives the size of the datatype rather than actual data value length.But I donot know what other function to suggest. Can any one comment on the observations I made.
Thanks
I have a problem with the QA guys.
select min(length(addr1)) from schema.tablename
gives 30. where addr1 is of varchar(30).
I think I have faced this issue even earlier. The length() in db2 gives the size of the datatype rather than actual data value length.But I donot know what other function to suggest. Can any one comment on the observations I made.
Thanks
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
I tried with min(length(ltrim(rtrim(addr1)))) it is correctly giving as 1.ArndW wrote:I'm not at DB/2 right now, but what does "select min(length(TRIM(addr1))) from schema.tablename " return?
But when I use min(length(addr1)) I am getting 30. well the problem is QA is complaining about containing spaces well I used trim() in the derivation in transformer stage which should take care of removing spaces in datastage and since datatype is varchar(30) the extra spaces should also not have been added.Not really sure whats happening.
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
As wierd as it may sound the problem seems to be using LOAD method.
When I use write method I see spaces are being trimmed correctly but when I use LOAD method spaces are not trimmed( or being added back again).Is it because of any environmental variable setting?
When I use write method I see spaces are being trimmed correctly but when I use LOAD method spaces are not trimmed( or being added back again).Is it because of any environmental variable setting?
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Is there a CHAR_LENGTH function, or something similar?
Otherwise, use the Len() function within DataStage; the Len() function returns the number of characters in the string.
Otherwise, use the Len() function within DataStage; the Len() function returns the number of characters in the string.
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.
No, char_length() function or something similar is not there in db2 udb.
I found the problem is because of the LOAD method that's used when I changed it to write everything is getting trimmed correctly. Not sure if thats stage related (DB2 Enterprise Stage), I should explore other stages available for DB2.
I found the problem is because of the LOAD method that's used when I changed it to write everything is getting trimmed correctly. Not sure if thats stage related (DB2 Enterprise Stage), I should explore other stages available for DB2.
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson