Add trailing zeros to a number casted as text 2

[RustyAfro]

I want to take a number, 123456, and cast it to a char(8) and have it's format be '00123456'. Some numbers are less than 6 digits, such as 12345.

Is there a format function to accomplish this?

 

[LarrySteele]

I hope someone can come up with a better solution. This works, but it sure doesn't look very efficient:

select
left('00000000',8-length(rtrim(cast(122 as char(8))))) || cast(122 as char(8)) as my_value
from sysibm.sysdummy1

You would replace the literal 122 with your column name and change sysibm.sysdummy1 to your table name.

HTH,
Larry

 

[LKBrwnDBA]

Try:

REPEAT('0',8-LENGTH(thenumber))||thenumber
-- OR --
RIGHT(CONCAT('00000000',thenumber),8)
-- OR (many other combinations)

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

PS: CAST the above to char(8).


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[RustyAfro]

Thanks guys ;-)

In your opinion, Would the concant be faster for the database to perform than the 8-length method?

 

[LKBrwnDBA]

About the same.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LarrySteele]

Glad I could help.

I think I like the concat version better - fewer function calls since you don't need to get the length of the casted string.

One thing though, you'll need to rtrim() the casted value. Since it's casted as char(8), it will be forward filled with spaces to make the length 8. You'll want to trim the spaces before concatenating with zeros.

HTH,
Larry