1 as 0001 - 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)

1 as 0001

Author

Topic

dass05555
Yak Posting Veteran

55 Posts

Posted - 2008-03-06 : 00:37:51

hi friends...
good morning,

i want to get the integer 1 as 0001 in a table,
is there any keyword for this...

------------------
id | cname
------------------
0001 | king
0002 | hall
0003 | jack
. | .
. | .
. | .
1000 | don

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-06 : 00:45:20

Do not cross post:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98521

CODO ERGO SUM

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-03-06 : 01:04:22

select right('0000' + convert(varchar,myintcol),4)

elsasoft.org

dass05555
Yak Posting Veteran

55 Posts

Posted - 2008-03-06 : 03:49:49

Thanks for u'r help...

regards,
Aruldass.B

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-06 : 07:47:17

quote:
Originally posted by jezemine

select right('0000' + convert(varchar,myintcol),4)

elsasoft.org

Just three zeroes are enough

select right('000' + convert(varchar,myintcol),4)

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-06 : 07:52:45

quote:
Originally posted by jezemine

select right('0000' + convert(varchar,myintcol),4)

elsasoft.org

and you should be very careful if you dont specify the length during convertion

select convert(varchar,'see if you can read this hint fully')
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-03-06 : 09:20:01

My personal favorite:

SELECT REPLICATE('0', 4-LEN(ID)) + CAST(ID AS VARCHAR)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-06 : 09:36:55

Or just this:

select right(10000+ID,4)

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-06 : 09:48:05

quote:
Originally posted by Michael Valentine Jones

Or just this:
select right(10000+ID,4)

CODO ERGO SUM

Yes Thats the power of implicit convertion as you dont need cast or convert when you use string functions over numbers

select right(year(getdate()),2)
select 'Year '+ltrim(year(getdate()))

Madhivanan

Failing to plan is Planning to fail

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-03-06 : 23:48:29

Why would anyone do this to a perfectly good integer? This kind of stuff should be done in the GUI, no?

--Jeff Moden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-07 : 00:10:55

Jeff, you want actual reasons for doing things?

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-07 : 03:09:36

quote:
Originally posted by Jeff Moden

Why would anyone do this to a perfectly good integer? This kind of stuff should be done in the GUI, no?

--Jeff Moden

Jeff, I am happy to hear this from you

Madhivanan

Failing to plan is Planning to fail

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-03-07 : 19:04:58

Heh... it's not so much because of the formatting, although thanks to you and Jeff Smith, I've learned that lesson... it's because they've just limited themselves to 9,999 numbers and they'll probably have to do something stupid like reuse some of those numbers when they run out (they ALWAYS run out). If no GUI is involved, I can maybe see an on the fly conversion, but certainly not saving it in a table that way.

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources