Right Align the character field Value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pioneer - 12 Jan 2006 13:40 GMT

Hi,

column nme: TEMP
Datatype: CHAR length: 6

the value we are getting is

TEMP
2X ,
SWD ,
1QER ,

What we want is

00002X,
000SWD,
001QER

Basically we want to rigth align the fields firstand then replace the
spaces by zeros.

Regards,
Pioneer

Reply to this Message

Knut Stolze - 12 Jan 2006 16:08 GMT

> Hi,
>
[quoted text clipped - 16 lines]
> Basically we want to rigth align the fields firstand then replace the
> spaces by zeros.

Have a look at the REPEAT, CONCAT, and LENGTH functions.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Serge Rielau - 12 Jan 2006 16:57 GMT

> Hi,
>
[quoted text clipped - 16 lines]
> Basically we want to rigth align the fields firstand then replace the
> spaces by zeros.

Thsi should do:
SUBSTR((SUBSTR('000000', 1, 6 - LENGTH(RTRIM(TEMP)))) || TEMP, 1, 6)

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

Reply to this Message

vijayk - 12 Jan 2006 17:05 GMT

This should work:

RIGHT('000000'||RTRIM(CHAR(your_column_here)),6)

Reply to this Message

vijayk - 12 Jan 2006 17:07 GMT

This will work:

RIGHT('000000'||RTRIM(CHAR('XXX')),6)

Vijayk

> Hi,
>
[quoted text clipped - 19 lines]
> Regards,
> Pioneer

Reply to this Message

Phil Sherman - 12 Jan 2006 17:21 GMT

There's lots of ways of doing this:

select temp,substr(right('000000' concat rtrim(temp),6),1,6) from t1

select temp,right('000000' concat rtrim(temp),6) from t1

Excluding the first column of the output; the first yields a 6 byte
result column. The second outputs 6 bytes for each row but the 6 bytes
is in a 4k varchar column, the length of the output of the right
function. If you run these through the command prompt, then the second
will output 4k long rows.

Phil Sherman

> Hi,
>
[quoted text clipped - 19 lines]
> Regards,
> Pioneer

Reply to this Message