Page 1 of 1
Length(Varchar) in db2
Posted: Mon Oct 02, 2006 9:48 am
by kris007
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
Posted: Mon Oct 02, 2006 9:50 am
by ArndW
I'm not at DB/2 right now, but what does "select min(length(TRIM(addr1))) from schema.tablename " return?
Posted: Mon Oct 02, 2006 10:01 am
by kris007
ArndW wrote:I'm not at DB/2 right now, but what does "select min(length(TRIM(addr1))) from schema.tablename " return?
I tried with min(length(ltrim(rtrim(addr1)))) it is correctly giving as 1.
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.
Posted: Mon Oct 02, 2006 10:08 am
by kris007
Does trim() take care of whitespace too? Or should I use stripwhitespace() to remove them explicitly.
Posted: Mon Oct 02, 2006 11:55 am
by thumsup9
Kris,
What happens when you reload your data using Trim(Convert(Char(000),'',Input)) instead of Trim() in your transformer stage.
Thums!!
Posted: Mon Oct 02, 2006 1:49 pm
by kris007
I ran the same job in a different schema limiting the number of records to only 1000 now I see there are no spaces but when I ran it using LOAD method back in the dev schema I see the spaces in the same records.
Posted: Mon Oct 02, 2006 2:18 pm
by kris007
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?
Posted: Mon Oct 02, 2006 3:09 pm
by ray.wurlod
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.
Posted: Tue Oct 03, 2006 12:40 pm
by kris007
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.