Formatting numbers

Any help on this problem would be greatly appreciated.
I have a field in my database that contanis numbers (i.e. 1, 2, 6, 7
etc.)
I want to create a stored procedure to return these numbers, but
formatted in such a way that they always show a full 10 digits. For
example 7 would be 0000000007 and 243 would be 0000000243 and 120000007
would be 0120000007 etc.
I know in Access something like this could be achieved by doing
format$([MyNumber],"0000000000"). Anyone know how I can do this in SQL?
-JoelSELECT RIGHT('0000000000' + CAST(YourNumberCol AS VARCHAR(10)),10)
Andrew J. Kelly SQL MVP
<jsnation@.gmail.com> wrote in message
news:1133567995.941523.96000@.f14g2000cwb.googlegroups.com...
> Any help on this problem would be greatly appreciated.
> I have a field in my database that contanis numbers (i.e. 1, 2, 6, 7
> etc.)
> I want to create a stored procedure to return these numbers, but
> formatted in such a way that they always show a full 10 digits. For
> example 7 would be 0000000007 and 243 would be 0000000243 and 120000007
> would be 0120000007 etc.
> I know in Access something like this could be achieved by doing
> format$([MyNumber],"0000000000"). Anyone know how I can do this in SQL?
> -Joel
>|||Formatting should be done in your frontend not in SQLServer, but you
could consider this one here:
DECLARE @.Number VARCHAR(10)
SET @.Number = 2
SELECT RIGHT('0000000000' + @.Number,10)
HTH, Jens Suessmeyer.|||DECLARE @.temp TABLE ( test_number INT IDENTITY )
DECLARE @.i INT
SET NOCOUNT ON
SET @.i = 1
-- Insert some dummy values
WHILE @.i < 100
BEGIN
INSERT INTO @.temp DEFAULT VALUES
SET @.i = @.i + 1
END
SET NOCOUNT OFF
SELECT REPLICATE ( 0 , 10-LEN( test_number ) ) + CAST( test_number AS
VARCHAR )
FROM @.temp
-- Damien
"jsnation@.gmail.com" wrote:

> Any help on this problem would be greatly appreciated.
> I have a field in my database that contanis numbers (i.e. 1, 2, 6, 7
> etc.)
> I want to create a stored procedure to return these numbers, but
> formatted in such a way that they always show a full 10 digits. For
> example 7 would be 0000000007 and 243 would be 0000000243 and 120000007
> would be 0120000007 etc.
> I know in Access something like this could be achieved by doing
> format$([MyNumber],"0000000000"). Anyone know how I can do this in SQL?
> -Joel
>|||>> have a field [sic] in my database ..<<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files.
The basic principle of a tiered architecture is that display is done in
the front end and never in the back end. This a more basic programming
principle than just SQL and RDBMS.
You are asking BAAAAAD questions from fundamental ignorance. Please
get a few eyars under your belt before you try to write a database.
Remember, it takes SIX years to become a Union Journeyman Carpenter
(NOT a Master!) in New York State. How long have you been writing SQL?|||Wrong! Formatting (display) is done where it is most efficient and scalable
to do it. We aren't using mainframes anymore, it matters how much data is
passed between the server and client/middle tier.
The most basic programming principle is that you look at your architecture
and design for what you have and not implement definitive statements likes
yours willy nilly.
Consider - paging, pivoting etc... is it really efficient to pass back a
million rows to the client just to get the second page of 50 rows? Nope, it
isn't - but thats what your statement proposes.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1133666034.583452.191970@.g43g2000cwa.googlegroups.com...
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files.
>
> The basic principle of a tiered architecture is that display is done in
> the front end and never in the back end. This a more basic programming
> principle than just SQL and RDBMS.
> You are asking BAAAAAD questions from fundamental ignorance. Please
> get a few eyars under your belt before you try to write a database.
> Remember, it takes SIX years to become a Union Journeyman Carpenter
> (NOT a Master!) in New York State. How long have you been writing SQL?
>|||Hi
I think, most efficient way will be
Select Replace(Str(m, n), ' ', '0')
Where m is actual number and n is required length
e.g. Select Replace(Str(8, 10), ' ', '0')
will return 0000000008
Prashant Deshmukh
"jsnation@.gmail.com" wrote:

> Any help on this problem would be greatly appreciated.
> I have a field in my database that contanis numbers (i.e. 1, 2, 6, 7
> etc.)
> I want to create a stored procedure to return these numbers, but
> formatted in such a way that they always show a full 10 digits. For
> example 7 would be 0000000007 and 243 would be 0000000243 and 120000007
> would be 0120000007 etc.
> I know in Access something like this could be achieved by doing
> format$([MyNumber],"0000000000"). Anyone know how I can do this in SQL?
> -Joel
>sql