Formatting numbers

3 Dec 2005 12:03 AM

jsnation

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

3 Dec 2005 3:08 AM

Andrew J. Kelly

SELECT RIGHT('0000000000' + CAST(YourNumberCol AS VARCHAR(10)),10)

--
Andrew J. Kelly SQL MVP

<jsnat***@gmail.com> wrote in message

Show quote

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
>

3 Dec 2005 8:22 AM

Jens

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.

3 Dec 2005 1:06 PM

Damien

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

Show quote

"jsnat***@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
>
>

4 Dec 2005 3:13 AM

--CELKO--

>> 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.

>> I want to create a stored procedure to return these numbers, but formatted in such a way .. <<

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?

4 Dec 2005 9:51 AM

Tony Rogerson

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

Show quote

"--CELKO--" <jcelko***@earthlink.net> wrote in message
news:1133666034.583452.191970@g43g2000cwa.googlegroups.com...
>>> 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.
>
>>> I want to create a stored procedure to return these numbers, but
>>> formatted in such a way .. <<
>
> 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?
>

16 Dec 2005 4:15 AM

Prashant D.

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

Show quote

"jsnat***@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
>
>