Plenty of padding - 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 2000 Forums

Transact-SQL (2000)

Plenty of padding

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-04-19 : 08:06:01

I'm now losing the plot. I was sure there was a function that does this but can't find it

I want to convert a number into a padded (to n characters) character type

so for example (assuming I'm padding to 5 characters) then I should get

1 -> 00001
20 -> 00020
1234 -> 01234

etc.

Many thanks

steve

A sarcasm detector, what a great idea.

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-19 : 08:08:17

SELECT RIGHT('0000'+convert(varchar(5),yourfield),5) from yourtable

Should do it

-------
Moo. :)

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-04-20 : 02:48:24

Thanks

steve

A sarcasm detector, what a great idea.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-04-20 : 04:47:54

also:

declare @n int
set @n=5
select replicate('0',@n-len(data)) + data from tableX

--------------------
keeping it simple...

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-04-21 : 03:14:26

Cheers Jen, I've already implemented what Mr Mist suggested but when I get a moment I'll look at the execution plans for them both

thanks

steve

A sarcasm detector, what a great idea.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources