Length(Varchar) in db2

kris007 · Post by **kris007** » Mon Oct 02, 2006 9:48 am

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

ArndW · Post by **ArndW** » Mon Oct 02, 2006 9:50 am

I'm not at DB/2 right now, but what does "select min(length(TRIM(addr1))) from schema.tablename " return?

kris007 · Post by **kris007** » Mon Oct 02, 2006 10:01 am

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.

kris007 · Post by **kris007** » Mon Oct 02, 2006 10:08 am

Does trim() take care of whitespace too? Or should I use stripwhitespace() to remove them explicitly.

thumsup9 · Post by **thumsup9** » Mon Oct 02, 2006 11:55 am

Kris,

What happens when you reload your data using Trim(Convert(Char(000),'',Input)) instead of Trim() in your transformer stage.

Thums!!

kris007 · Post by **kris007** » Mon Oct 02, 2006 1:49 pm

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.

kris007 · Post by **kris007** » Mon Oct 02, 2006 2:18 pm

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?

ray.wurlod · Post by **ray.wurlod** » Mon Oct 02, 2006 3:09 pm

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.

kris007 · Post by **kris007** » Tue Oct 03, 2006 12:40 pm

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.