How to convert 1 to 01? - 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

Transact-SQL (2005)

How to convert 1 to 01?

Author

Topic

ryanlcs
Yak Posting Veteran

62 Posts

Posted - 2010-01-25 : 05:06:31

How can I convert 1 to 01, or 6 to 06, 12 to 12?
Thanks.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-25 : 05:17:09

create table #temp (val int)

insert into #temp
select 1 union
select 2 union
select 6 union
select 7 union
select 12

select * from #temp

select case when len(val)=1 then '0'+cast(val as char(1)) else cast(val as varchar(10)) end
from #temp

Note:- Now the data is converted as varchar.

Better you do the formatting in front end!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-01-25 : 05:27:27

Hello,
I don't know why you'd want to do this using the database engine, this should probably be handled by the front end as it looks like a presentation requirement. Having leading zeros means you're not storing a number, but a string, but I'm guessing all these codes are made up of numbers and have zeros to make them a fixed length. In that case, you should store the number and then add the zeros (if they are really required) when showing the output to the user.

However, this is how I would do it if I really needed to:


DECLARE @Number int=5
DECLARE @Zeros varchar(20)='0000000000'
DECLARE @Length int=3

SELECT  
	Substring(@Zeros,1,@Length-Len(@Number)) + CAST(@Number as varchar(20))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-25 : 05:27:57

SELECT REPLACE(STR(val, 2), ' ', '0')
FROM #Temp

N 56°04'39.26"
E 12°55'05.63"

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-01-25 : 05:30:05

This is probaby a better way to write that example:




DECLARE @Zeros varchar(20)='0000000000'
DECLARE @Length int=2	--change this to any length

SELECT  
	Substring(@Zeros,1,@Length-Len(Number)) + CAST(Number as varchar(20))
FROM
	(
		SELECT Number=1
		UNION ALL
		SELECT 6
		UNION ALL
		SELECT 12
	) A

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-25 : 05:32:37

SELECT REPLACE(STR(val, @Length), ' ', '0')
FROM #Temp

N 56°04'39.26"
E 12°55'05.63"

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-01-25 : 05:39:35

Think mine's faster Peso :)
Peso's Mine
Client processing time 368 151 259.5000
Total execution time 376 156 266.0000
Wait time on server replies 8 5 6.5000



DECLARE @Zeros varchar(20)='0000000000'
DECLARE @Length int=10	--change this to any length
;
WITH Numbers AS(
	SELECT Number=1
	UNION ALL
	SELECT Number=Number+1 FROM Numbers WHERE Number<30000
)

SELECT  
	REPLACE(STR(Number, @Length), ' ', '0'),
	Substring(@Zeros,1,@Length-Len(Number)) + CAST(Number as varchar(20))
FROM
	Numbers
OPTION(MAXRECURSION 0)

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-01-25 : 05:42:20

Sorry, that was supposed to read:


				Peso's	 Mine
Client processing time		368	 151	 259.5000
Total execution time		376	 156	 266.0000
Wait time on server replies	8	 5	 6.5000

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-25 : 05:58:23

Oh, you went for speed! I went for simplicity alone...
Ok then, try this for both simplicity and speed:

SELECT RIGHT('00000000000000000000' + cast(Number As varchar(20)), @Length)

N 56°04'39.26"
E 12°55'05.63"

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-01-25 : 06:36:57

Yes, you beat me! I was expecting that actually, thought mine was a bit complicated. They're actually both much closer than I expected though (average over 5 times):


				Mine		Peso's
  Client processing time	180.6000	166.2000
  Total execution time	177	186.4000	172.2000
  Wait time on server replies	5.8000		6.0000

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-25 : 09:03:57

quote:
Originally posted by ryanlcs

How can I convert 1 to 01, or 6 to 06, 12 to 12?
Thanks.

I guess you are trying to convert month value to two digits. If so, why are you doing so? Where do you want to use?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources