469,929 Members | 1,776 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,929 developers. It's quick & easy.

Right Align the character field Value

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

Jan 12 '06 #1
5 12249
Pioneer wrote:
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.


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

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Jan 12 '06 #2
Pioneer wrote:
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.

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

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
Jan 12 '06 #3
This should work:

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

Jan 12 '06 #4
This will work:

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

Vijayk
Pioneer wrote:
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


Jan 12 '06 #5
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
Pioneer wrote:
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

Jan 12 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by praba kar | last post: by
11 posts views Thread by Markus Mueller | last post: by
3 posts views Thread by kai | last post: by
3 posts views Thread by Wardeaux | last post: by
2 posts views Thread by Hans Kesting | last post: by
4 posts views Thread by Steve Swift | last post: by
1 post views Thread by David Greenberg | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.