469,167 Members | 1,256 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

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
Jul 23 '05 #1
6 9456
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
--
Jul 23 '05 #2
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

Jul 23 '05 #3
I usually use the following when trying to zero-pad numbers:

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

-Tom.

Jul 23 '05 #4
"David Portas" <RE****************************@acm.org> wrote in message news:<-o********************@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
Jul 23 '05 #5
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.

Jul 23 '05 #6
Don Croata (el*******@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Gani Pola | last post: by
5 posts views Thread by Tom Pair | last post: by
15 posts views Thread by Fritz Switzer | last post: by
8 posts views Thread by bienwell | last post: by
2 posts views Thread by Li Pang | last post: by
7 posts views Thread by pamela fluente | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.