Format display of current row

[Don Croata]

Hi!

I'm wondering is there any simple way to achieve the following
function call in SQL Server. The sentence to translate is (Oracle
syntax):

to_char(rownum, '000')

rownum: number of the current row

to_char: formats a number (the 1st param) according to the format
defined in the 2nd param. In this case, the '000' preprends 2 or more
zeros until forming a 3-digit number.

I'm using it in something like:

SELECT id_table, string_column || TO_CHAR(ROWNUM, '000') FROM table



Thanx a bunch,
Cro

[David Portas]

Re: Format display of current row

No such thing as a "row number" in SQL Server. Explain what you want to do
so that we can suggest some alternatives.

The Standard SQL alternative to TO_CHAR is the CAST function (CAST(x AS
VARCHAR) or CAST(x AS CHAR)) and that is supported by SQL Server.

--
David Portas
SQL Server MVP
--

[Alex]

Re: Format display of current row

replace(str(123 , 3,0),' ', '0')

where 123 is your number, 3 is the lenght(number of zeros to padd) and
0 is the number of decimals places to show

yuck, but it works

[Thomas R. Hummel]

Re: Format display of current row

I usually use the following when trying to zero-pad numbers:

SELECT RIGHT('000' + CAST(my_num AS VARCHAR), 3)

-Tom.

[Don Croata]

Re: Format display of current row

"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message news:<-oWdnSaXw8NBEfXf RVn-iA@giganews.com >...[color=blue]
> No such thing as a "row number" in SQL Server. Explain what you want to do
> so that we can suggest some alternatives.
>
> The Standard SQL alternative to TO_CHAR is the CAST function (CAST(x AS
> VARCHAR) or CAST(x AS CHAR)) and that is supported by SQL Server.[/color]

The idea behind is creating a stored procedure that retrieves a
numbered list of elements. This list could be displayed in the web, so
it needs to have that "rownum":

SELECT rn, el
FROM (
SELECT <<rownum>> AS rn, element AS el
FROM table
) AS tmp

I've seen some approaches using rank=count(*), but they look ugly.
Could I use something like a temporal sequence in SQL Server?


Thanx again,
Cro

[--CELKO--]

Re: Format display of current row

Why not create the table with a unique column for the display order of
the list elements? If the column is integer, you willnot have to pad a
string with zeroes -- that kind of display is supposed to be done in
the front end, not the database.

[Erland Sommarskog]

Re: Format display of current row

Don Croata (el.croata@gmai l.com) writes:[color=blue]
> The idea behind is creating a stored procedure that retrieves a
> numbered list of elements. This list could be displayed in the web, so
> it needs to have that "rownum":
>
> SELECT rn, el
> FROM (
> SELECT <<rownum>> AS rn, element AS el
> FROM table
> ) AS tmp
>
> I've seen some approaches using rank=count(*), but they look ugly.
> Could I use something like a temporal sequence in SQL Server?[/color]

There is a row_number() function in SQL 2005, currently in beta.
Beware that this is not a row_number of Oracle fame, but one
related to the actual result set. The syntax is also a little more
complicated. If memory serves, this is derived from the SQL-99 standard.

For SQL-2000 the best bet is probably to say:

CREATE TABLE #t (row_number int IDENTITY,
col1 ....)
INSERT #t (col1, )
SELECT col1, ...
FROM ...
ORDER BY

Note that you cannot use SELECT INTO for this, as it's not guaranteed
that the identity value will follow the ORDER BY clause in this case.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp