Counting the Number of Characters in a column

DEREZ LUSK

Counting the Number of Characters in a column

In DB2 zOS V10 - I have a column defined as CHAR 14 not nullable  when trying to count the characters 

in the column   CHARACTER_LENGTH(PROCD_DIAG_CD_VAL,OCTETS)

the result set is always 14    even if the column has the value of  'ABCDEF'

Is there a way to accurately get the number of characters of this column?

SELECT  PROCD_MSTR_KEY AS "MSTR_KEY",                       
        PROCD_DIAG_CD_VAL AS "CODE" ,                       
        CHARACTER_LENGTH(PROCD_DIAG_CD_VAL) AS "LENGTH"

       FROM DBID0021.PROCD_MSTR

       WITH UR;

 

Derez D. Lusk
BlueCross BlueShield of Il/TX/OK/NM/MT
Chicago, Illinois 60601

John Ryan

Counting the Number of Characters in a column
(in response to DEREZ LUSK)
Derez,

Use a CASE statement along with SUBSTR to test each character of the column for X'40' (space).

John

From: DEREZ LUSK [mailto:[login to unmask email]
Sent: Friday, March 28, 2014 12:53 PM
To: [login to unmask email]
Subject: [DB2UGCENPA] - Counting the Number of Characters in a column


In DB2 zOS V10 - I have a column defined as CHAR 14 not nullable when trying to count the characters

in the column CHARACTER_LENGTH(PROCD_DIAG_CD_VAL,OCTETS)

the result set is always 14 even if the column has the value of 'ABCDEF'

Is there a way to accurately get the number of characters of this column?

SELECT PROCD_MSTR_KEY AS "MSTR_KEY",
PROCD_DIAG_CD_VAL AS "CODE" ,
CHARACTER_LENGTH(PROCD_DIAG_CD_VAL) AS "LENGTH"

FROM DBID0021.PROCD_MSTR

WITH UR;



Derez D. Lusk
BlueCross BlueShield of Il/TX/OK/NM/MT
Chicago, Illinois 60601

-----End Original Message-----

Michael Gordon

RE: Counting the Number of Characters in a column
(in response to DEREZ LUSK)

If all of the data is left justified in the column then RTRIM will do it.

Example:

LENGTH(RTRIM(PROCD_DIAG_CD_VAL)

or

CHARACTER_LENGTH(RTRIM(PROCD_DIAG_CD_VAL),OCTETS)

DEREZ LUSK

RE: Counting the Number of Characters in a column
(in response to Michael Gordon)



In Reply to Michael Gordon:

If all of the data is left justified in the column then RTRIM will do it.

Example:

LENGTH(RTRIM(PROCD_DIAG_CD_VAL)

or

CHARACTER_LENGTH(RTRIM(PROCD_DIAG_CD_VAL),OCTETS)

 

yes Data is left justified  -  Robert Somers  provided: 

CHARACTER_LENGTH(STRIP(PROCD_DIAG_CD_VAL),OCTETS)

Derez D. Lusk
BlueCross BlueShield of Il/TX/OK/NM/MT
Chicago, Illinois 60601

DEREZ LUSK

RE: Counting the Number of Characters in a column
(in response to Michael Gordon)



In Reply to Michael Gordon:

If all of the data is left justified in the column then RTRIM will do it.

Example:

LENGTH(RTRIM(PROCD_DIAG_CD_VAL)

or

CHARACTER_LENGTH(RTRIM(PROCD_DIAG_CD_VAL),OCTETS)



ROBERT SOMERS PROVIDED 

Robert Somers  provided: 

CHARACTER_LENGTH(STRIP(PROCD_DIAG_CD_VAL),OCTETS)

which is basically the same 

Thanks !!

Derez D. Lusk
BlueCross BlueShield of Il/TX/OK/NM/MT
Chicago, Illinois 60601

DEREZ LUSK

RE: Counting the Number of Characters in a column
(in response to John Ryan)



In Reply to John Ryan:

Derez,

Use a CASE statement along with SUBSTR to test each character of the column for X'40' (space).

John

From: DEREZ LUSK [mailto:[login to unmask email]
Sent: Friday, March 28, 2014 12:53 PM
To: [login to unmask email]
Subject: [DB2UGCENPA] - Counting the Number of Characters in a column


In DB2 zOS V10 - I have a column defined as CHAR 14 not nullable when trying to count the characters

in the column CHARACTER_LENGTH(PROCD_DIAG_CD_VAL,OCTETS)

the result set is always 14 even if the column has the value of 'ABCDEF'

Is there a way to accurately get the number of characters of this column?

SELECT PROCD_MSTR_KEY AS "MSTR_KEY",
PROCD_DIAG_CD_VAL AS "CODE" ,
CHARACTER_LENGTH(PROCD_DIAG_CD_VAL) AS "LENGTH"

FROM DBID0021.PROCD_MSTR

WITH UR;



Derez D. Lusk
BlueCross BlueShield of Il/TX/OK/NM/MT
Chicago, Illinois 60601

-----End Original Message-----

Robert Somers  provided: 

CHARACTER_LENGTH(STRIP(PROCD_DIAG_CD_VAL),OCTETS)

 

Thanks

Derez D. Lusk
BlueCross BlueShield of Il/TX/OK/NM/MT
Chicago, Illinois 60601