var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Db2 Substr
Running on a mainframe. ZOS, DB2 V7.2.
database field is defined as
PSTL_CDE DECIMAL(9) NOT NULL
I am trying to switch it to a CHAR field to preserve leading zeros. (actually it's a bit more complicated than that, because it's used in a View with another table that's defined as a char.)
But here is my question. I have several different ways to select and convert the field. Ideally, I want a 9 character field, no decimal point. Just like if it was defined as CHAR(09).
Here's a few of my results
ORIG DEC9 = "140631834."
CHAR(PSTL_CDE) = "140631834." => I don't want the decimal point
SUBSTR(CHAR(ADDR_PSTL_CDE),1,09) = "14063183" => Missing the '4'.
SUBSTR(CHAR(ADDR_PSTL_CDE),1,10) = "140631834" => the number I want, but now it's CHAR(10). Not a huge deal, but why do I have to use 10?
Why do I have to make it a CHAR(10)? The DB2 manual (pg 283, dsnsqh12) says the length parm should be Length - Start + 1 (9-1+1) or 9.
Syntax is SUBSTR(string, start, length).
Last edited by edrenckh; 08-18-04 at
I think it has something to do with the CHAR function. According to the SQL reference, for "Decimal to Character" "the lenght of the result is 2+p, where p is the precision of the decimal-expression. (In your case 2+9 = 11)