DB2 Query

Prasanthhere · Active User Joined: 03 Aug 2005 Posts: 306

Hi

I am having a table suppose its having 3 fields which are integer .Let the length of each field FIELD1 is 8,FIELD1 is 9,FIELD3 is 7 .I am having values 3333 4444 5555 in the corresponding fields .i want to write an SQL query which will pad zeores to the left how can we do that

Eg

Input table A

FIELD1 FIELD2 FIELD3
3333 4444 5555

I want the o/p to be

FIELD1 FIELD2 FIELD3
00003333 000004444 000555

Thanks in advance

Hanfur · Active User Joined: 21 Jun 2006 Posts: 104

Prsanth,

If you want to pad with zero to coumn values use 0 in single quotes as given below..

Here CMPY,LEDGR,NAME are column's of table TABLE1.

SELECT ('0000'||CMPY),('00000'||LEDGR),('000'||NAME)
FROM DB2TST.TABLE1

CMPY --> padded with 0000 zeros
LEDGR --> padded with 00000 zeros and
Name --->padded with 000 zeros.

-Han.

DavidatK · Posted: Wed Jul 26, 2006 12:42 am

Hello Han,

See if this is what you are looking for. If not, please come back with furthur expination.

ravi17s · New User Joined: 15 Aug 2003 Posts: 57

Prasanth,

Putting togather David's and Hanfur's Ideas......This is what i get...

SELECT DIGITS(CAST(COLUMNN1 AS DECIMAL(8,2))),
DIGITS(CAST(COLUMNN2 AS DECIMAL(9,2))),
DIGITS(CAST(COLUMNN1 AS DECIMAL(7,2))),

FROM <tablename>
WHERE <CONDITIOn> ;

Please let me know if you need further clarification...

Thanks,
Ravi..........

ravi17s · New User Joined: 15 Aug 2003 Posts: 57

This is more appropiate........than my previous query...

SELECT DIGITS(CAST(COLUMNN1 AS DECIMAL(8,0))),
DIGITS(CAST(COLUMNN2 AS DECIMAL(9,0))),
DIGITS(CAST(COLUMNN1 AS DECIMAL(7,0))),

FROM <tablename>
WHERE <CONDITIOn> ;

Thanks,
Ravi.........