Tags: actual, char, database, db2, emp, field, mysql, named, oracle, platform, size80, sql, string, table, type, version, windows

How to find actual LENGTH of a CHAR string

On Database » DB2

9,518 words with 9 Comments; publish: Tue, 20 May 2008 00:37:00 GMT; (25054.69, « »)

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

All Comments

Leave a comment...

  • 9 Comments
    • In article <1106028963.012207.83870.db2.todaysummary.com.c13g2000cwb.googlegroups.com>,

      (mailar.db2.todaysummary.com.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.

      #1; Tue, 20 May 2008 00:39:00 GMT
    • 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.

      #2; Tue, 20 May 2008 00:40:00 GMT
    • mailar.db2.todaysummary.com.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

      #3; Tue, 20 May 2008 00:41:00 GMT
    • Gert van der Kooij wrote:

      > In article <1106034350.350697.321050.db2.todaysummary.com.z14g2000cwz.googlegroups.com>,

      > (mailar.db2.todaysummary.com.gmail.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.

      Knut Stolze

      Information Integration

      IBM Germany / university of Jena

      #4; Tue, 20 May 2008 00:42:00 GMT
    • In article <csihlg$1f1$1.db2.todaysummary.com.fsuj29.rz.uni-jena.de>, Knut Stolze

      (stolze.db2.todaysummary.com.de.ibm.com) says...

      > 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.

      #5; Tue, 20 May 2008 00:43:00 GMT
    • Try length(ltrim(rtrim(NAME)))

      HTH

      Joachim

      <mailar.db2.todaysummary.com.gmail.com> schrieb im Newsbeitrag

      news:1106028963.012207.83870.db2.todaysummary.com.c13g2000cwb.googlegroups.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

      >

      #6; Tue, 20 May 2008 00:44:00 GMT
    • mailar.db2.todaysummary.com.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

      #7; Tue, 20 May 2008 00:45:00 GMT
    • mailar.db2.todaysummary.com.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

      damorgan.db2.todaysummary.com.x.washington.edu

      (replace 'x' with 'u' to respond)

      --== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet New

      s==--

      http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000

      Newsgroups

      --= East/West-Coast Server Farms - Total Privacy via Encryption =--

      #8; Tue, 20 May 2008 00:46:00 GMT
    • 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:

      > mailar.db2.todaysummary.com.gmail.com wrote:

      part[vbcol=seagreen]

      > 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

      > damorgan.db2.todaysummary.com.x.washington.edu

      > (replace 'x' with 'u' to respond)

      >

      > --== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet

      News==--

      > http://www.webservertalk.com The #1 Newsgroup Service in the World!

      >100,000 Newsgroups

      > --= East/West-Coast Server Farms - Total Privacy via Encryption =--

      #9; Tue, 20 May 2008 00:47:00 GMT