Results 1 to 5 of 5

Thread: phantom column

  1. #1
    Join Date
    Oct 2013

    Exclamation Unanswered: phantom column

    Hello there,

    Could anyone help me with the following question?

    A table column named TEXT (data type:character, length:200, null:yes) gives me some trouble.

    - if I select the column from the table the result is blank
    - if I select trim(length(text)) the result is 73
    - if I do the following select :
    select text || 'asd' from table
    => the result is blank again (and not 73 spaces followed by 'asd' as I expected)
    - the value in the column is not NULL ( tested using "is null")

    Could anyone tell me what does this column contain?
    I have no idea.

  2. #2
    Join Date
    Apr 2012
    Provided Answers: 27
    What is the binary or hex value in the column ? i.e. select hex(text) from ....
    Does the column allow NULL ? are all the values NULL ? select coalesce(text,'blank') from ...

    Also, what is the database codepage, and your application-codepage, and are you using a GUI or a command-line to view the result of the select ?
    Always give you db2 server version + fixpack, and operating system name + version.

  3. #3
    Join Date
    Oct 2013

    Thumbs up


    - hex value: all space 202020...
    - the column allows null
    - the select coalesce... returns the same result as the simple select

    - database codepage: 819
    - db2 version : v9.5.0.10
    - o.s.: Windows xp but i use a unix server

  4. #4
    Join Date
    Apr 2012
    Provided Answers: 27
    So your datatype for TEXT might be CHAR(73) and that is the expected behaviour.
    If the datatype was VARCHAR(73) you may get a different result, as regards the length()

  5. #5
    Join Date
    Oct 2013

    Red face

    TEXT is CHAR(200)

    - if I select length(text) the result is 200

    Maybe this could help:
    If i compare the row with the strange value in TEXT with another row in the table (with i am sure is empty):
    - select length(text) from table => result is 200 for both rows
    - select length(trim(text)) from table => result is 73 for the strange row and 0 for the empty row
    - select hex(text) from table => result is the same for both rows (2020202020...)
    - select coalesce(text, 'blank') from table => result is the same for both rows (blank)

    What is in that column that has length 73?
    If I select concat(text, 'string') from table => for the empty row the result is 200 spaces + 'string'(what i expect) but for the strange row the result is blank

    I don't think there is NULL value in the row beacouse if I select text from table where text is null the row is not returned.


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts