Link to home
Create AccountLog in
Avatar of RichNH
RichNH

asked on

T-SQL - Put leading zeros in front of Int

I would like to take an int data element and cast it as char(9) with leading zeros.  The value in the int fields can be up to 9 in length.  So, 1 is a valid value, so is 12345, so is 999999999.

My musings so far are this:
select CAST(RIGHT(('000000000' + 1234),9) AS char(9) )

select ('000000000' + cast(1234 as CHAR (20)))

select right(('000000000' + cast(1234 as CHAR (9))),9)

select right(CAST('000000000' + cast(1234 as CHAR (9)) AS CHAR(20)),9)

The second one ALMOST works, how can I just get the rightmost 9 characters and actually have it come out as char?  It seems wheh I do that (3rd example) it is displayed as an int.

I am running under MS SQL Server 2008.
SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of RichNH
RichNH

ASKER

Thank you for the responses,  Since the shop generally likes to stay with ANSI SQL I chose the second solution as the best but wanted to thank both of you.   I have learned something.