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