Put leading zero's in a varchar field until 5 len

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)

Put leading zero's in a varchar field until 5 len

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2009-09-02 : 19:19:57

Hi All,

I have a table called Table1 with a varchar field ID.
In my select I want to retrieve all the records but want to put leading zero's in the ID where the length is less than 5 characters. So for example following are some records;
ID
1234
12
12345
7
888

Now my select should retrieve the following.

ID_new
01234
00012
12345
00007
00888

How to do that in my SELECT query?
Thanks a million for the quick help.

Zee

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-02 : 19:24:51

select right(' ' + id, 5) from table1

hmm, a lot of questions like this lately...

Be One with the Optimizer
TG

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2009-09-02 : 19:29:11

TG you mean select right('0' + id, 5) from table1 . Right?

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2009-09-02 : 19:34:34

What you suggested is wrong. I think it should be the following.

SELECT right('00000' + id, 5) from table1

Can someone confirm please?

Thanks....

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-02 : 19:44:40

duh, yeah I meant to put zeros not spaces and since I didn't use [ code ] tags the extra spaces got nixed.

That looks good to me zee.

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-03 : 03:05:44

You can use many methods


declare @id varchar(5)
set @id='34'
SELECT 
	right('00000' + @id, 5) ,
	replace(str(@id,5),' ','0'),
	replicate('0',5-len(@id))+@id

PS : It should be done in front end application

Madhivanan

Failing to plan is Planning to fail

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2009-09-03 : 05:35:14

Thanks...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources