adding leading zero - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

adding leading zero

Author

Topic

beancounter
Starting Member

5 Posts

Posted - 2011-11-23 : 17:46:47

Hi I am new to SQL and trying to extract data into a report that my company needs.

I have a table with a field called occupant_code. The results can be either single digit 1 or 2 or 3 ... or two digit 10 or 11 ... is there a way i can format the column so if it is a single digit it will have a leading zero and if it is a two digit it will be left alone?

Here is what I have so far:

select account_no, occupant_code, current_balance from puaccbal Where current_balance <> 0.00
SELECT convert(varchar, getdate(), 120)

I appreciate any help,
John

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-24 : 06:14:08

select right('00' + col,2)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-25 : 04:53:28

As there is at least a single digit, this is enough

lect right('0' + col,2)

Madhivanan

Failing to plan is Planning to fail

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-25 : 04:58:26

Only if there is always a single digit - ok if you are converting from an int but not if dealing with a varchar.
imho it's always putting in the extra character as it doesn't cost much and might help.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-25 : 05:28:32

quote:
Originally posted by nigelrivett

Only if there is always a single digit - ok if you are converting from an int but not if dealing with a varchar.
imho it's always putting in the extra character as it doesn't cost much and might help.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Yes I agree. Also note that mostly this formation is applied on numbers

Madhivanan

Failing to plan is Planning to fail

pduffin
Yak Posting Veteran

68 Posts

Posted - 2011-11-30 : 18:55:50

create FUNCTION [dbo].[ZeroPrefix]
(@in int,@len int)
RETURNS char(20)
AS
BEGIN
declare @out char(20)
declare @zeros char(20)
select @zeros = rtrim(replace(space(@len),' ','0'))
select @out = right(rtrim(@zeros) + rtrim(cast(@in as char(20))),@len)
return @out
END

This function works for ints.

Likes to run, hates the runs!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources