Format display of current row

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

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

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

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

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

-Tom.

"David Portas" < REMOVE_BEFORE_REPLYI
NG_dportas@acm.org> wrote in message news:<-oWdnSaXw8NB
EfXfRVn-iA@giganews.com>...
> 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.

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

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.

Don Croata (el.croata@gmail.com)  writes:
> 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?

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@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp