How to find actual LENGTH of a CHAR string

[mailar]

Hi,
I am using DB2 version 8.2 on Windows XP platform.

I have a table 'EMP' with one field named 'NAME' of type CHAR , size
80.

Also , I have a record with NAME='john'

Now, when I try to find the length of the this name field DB2 returns
80 . Can someone tell me how do I find the actual length of this NAME
field value ('john') without making use of RTRIM.

I say without using RTRIM because, I want to be able to find out the
length of char strings like ' john ' as well .
Thanks in advance
mailar

[Gert van der Kooij]

In article <11*********************@c13g2000cwb.googlegroups. com>,
(ma****@gmail.com) says...

Hi,
I am using DB2 version 8.2 on Windows XP platform.

I have a table 'EMP' with one field named 'NAME' of type CHAR , size
80.

Also , I have a record with NAME='john'

Now, when I try to find the length of the this name field DB2 returns
80 . Can someone tell me how do I find the actual length of this NAME
field value ('john') without making use of RTRIM.

I say without using RTRIM because, I want to be able to find out the
length of char strings like ' john ' as well .
Thanks in advance
mailar

You could make it a varchar or fill the remaining part of the column
with a special character.

[mailar]

Hi,
I do not want to make it a VARCHAR field.

Also , my application does not make sense if I fill the remaining part
with some other characters.

Is there a way by which I can figure out what value the user has
entered in the column and know the length of this value without
including the remaining white spaces padded by DB2.

[Gert van der Kooij]

In article <11**********************@z14g2000cwz.googlegroups .com>,
(ma****@gmail.com) says...

Hi,
I do not want to make it a VARCHAR field.

Also , my application does not make sense if I fill the remaining part
with some other characters.
You can't strip them before 'using' the original data?
Is there a way by which I can figure out what value the user has
entered in the column and know the length of this value without
including the remaining white spaces padded by DB2.

Not if you don't want to change it to varchar. Maybe adding a column
containing the original length will help?

[Knut Stolze]

ma****@gmail.com wrote:

Hi,
I do not want to make it a VARCHAR field.
Why don't you want to use VARCHAR?

CHAR(X) means that all strings stored in that column will always have a
length of X. If the strings are not long enough when you insert them, DB2
will pad whitespaces to bring them to the required length. That's the
definition of the CHARACTER data type.

VARCHAR(X) tells the database engine that you want to store strings that are
at most X bytes long. Internally, DB2 will store the information about the
actual length of a string, along with the string data itself. So you have
an overhead of at most 2 bytes for each value you insert. On the upside,
you store only "2 + n" bytes for each string, where "n" is the actual
length. That means, if your strings are usually shorter than X, you will
actually save space. And the next plus point is that the strings are
stored right as they are inserted without any padding taking place.
Is there a way by which I can figure out what value the user has
entered in the column and know the length of this value without
including the remaining white spaces padded by DB2.

Decide what you want:

- no padding --> use VARCHAR (or some work-around with special characters)
- CHARACTER --> your strings will be padded to the required length

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Knut Stolze]

Gert van der Kooij wrote:

In article <11**********************@z14g2000cwz.googlegroups .com>,
(ma****@gmail.com) says...

Is there a way by which I can figure out what value the user has
entered in the column and know the length of this value without
including the remaining white spaces padded by DB2.

Not if you don't want to change it to varchar. Maybe adding a column
containing the original length will help?

Ugh. That's just simulating VARCHAR on a higher level. I would
definitively push such logic to the DBMS and stick to VARCHAR in the first
place.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Gert van der Kooij]

In article <cs**********@fsuj29.rz.uni-jena.de>, Knut Stolze
(st****@de.ibm.com) says...

Not if you don't want to change it to varchar. Maybe adding a column
containing the original length will help?

Ugh. That's just simulating VARCHAR on a higher level. I would
definitively push such logic to the DBMS and stick to VARCHAR in the first
place.

Yep, that's right. I would never do that in my own application,
changing to varchar would be my choice.

[Joachim Klassen]

Try length(ltrim(rtrim(NAME)))

HTH
Joachim
<ma****@gmail.com> schrieb im Newsbeitrag
news:11*********************@c13g2000cwb.googlegro ups.com...

Hi,
I am using DB2 version 8.2 on Windows XP platform.

I have a table 'EMP' with one field named 'NAME' of type CHAR , size
80.

Also , I have a record with NAME='john'

Now, when I try to find the length of the this name field DB2 returns
80 . Can someone tell me how do I find the actual length of this NAME
field value ('john') without making use of RTRIM.

I say without using RTRIM because, I want to be able to find out the
length of char strings like ' john ' as well .
Thanks in advance
mailar

[Serge Rielau]

ma****@gmail.com wrote:

Hi,
I am using DB2 version 8.2 on Windows XP platform.

I have a table 'EMP' with one field named 'NAME' of type CHAR , size
80.

Also , I have a record with NAME='john'

Now, when I try to find the length of the this name field DB2 returns
80 . Can someone tell me how do I find the actual length of this NAME
field value ('john') without making use of RTRIM.

I say without using RTRIM because, I want to be able to find out the
length of char strings like ' john ' as well .
Thanks in advance
mailar

LENGTH(RTRIM(LTRIM(' john ')) =>4

[DA Morgan]

ma****@gmail.com wrote:

Hi,
I do not want to make it a VARCHAR field.

Also , my application does not make sense if I fill the remaining part
with some other characters.

Is there a way by which I can figure out what value the user has
entered in the column and know the length of this value without
including the remaining white spaces padded by DB2.

Are you a student? It makes no sense to use a fixed length data type
and then expect it to be something other than fixed length.

--
Daniel A. Morgan
University of Washington
da******@x.washington.edu
(replace 'x' with 'u' to respond)
----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---

[mailar]

Yes, I am a DB2 student.

By the way, can someone tell me that is there a way by which I can
temporarily change this default padding chracter from whitespace to
something else.
Also , is there a way to write a triger on built in functions like
CHAR() ?

Thanks in advance
mailar

DA Morgan wrote:

ma****@gmail.com wrote:

Hi,
I do not want to make it a VARCHAR field.

Also , my application does not make sense if I fill the remaining part with some other characters.

Is there a way by which I can figure out what value the user has
entered in the column and know the length of this value without
including the remaining white spaces padded by DB2.
Are you a student? It makes no sense to use a fixed length data type
and then expect it to be something other than fixed length.

--
Daniel A. Morgan
University of Washington
da******@x.washington.edu
(replace 'x' with 'u' to respond)
----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet

News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World!
100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---

[Arne Doe]

Serge Rielau schrieb:

ma****@gmail.com wrote:

I say without using RTRIM because, I want to be able to find out the
length of char strings like ' john ' as well .

LENGTH(RTRIM(LTRIM(' john ')) =>4

could be:
LENGTH(RTRIM(LTRIM('.' || ' john ' || '.'))) - 2 =>9