SQL Getting 2 digit numbers from row_number Over () function

Question

Is it possible to get the numbers with 2 digits using the following function?

Row_number () Over(Order By UH.Stuff) AS HSN

I want the query to return 01 , 02 , 03 instead of 1,2,3...

What about row with number 100? – Hamlet Hakobyan Mar 21 '14 at 11:19 — Hamlet Hakobyan, Mar 21 '14 at 11:19

score 2 · Accepted Answer · answered Mar 21 '14 at 11:20

2

Do something like this:

RIGHT('0' + CONVERT(VARCHAR,ROW_NUMBER() OVER (ORDER BY UH.Stuff)), 2) AS HSN

answered Mar 21 '14 at 11:20

Dan

10,480
23
49

What about row with number 100? – Hamlet Hakobyan Mar 21 '14 at 11:20
2

Using this solution, row number 100 will output as "00". Row number 101 will output as "01", and so on. But that was not the question. The question was how to output with only 2 digits, which is exactly what my solution does. I trust that OP knows how many rows his UH.Stuff table contains. – Dan Mar 21 '14 at 11:21
He only knows how many rows his UH.Stuff table contains *today*. IMHO, one should save the left-padding for the presentation layer, not do it the query. – alroc Mar 21 '14 at 11:26
1

@alroc - left-padding at the DB layer can be useful, for example if you want to generate sequential order numbers of type VARCHAR, that should be sortable in ascending order, ("ORD-0001", "ORD-0002", "ORD-0010", etc.), but other than that, I agree. This solutions depends upon the fact that OP knows for sure, that the UH.Stuff is a static table, where the number of records will never exceed 99. – Dan Mar 21 '14 at 11:42
as far as the logic goes,the numbers here could be only 2 digit numbers so this logic does it for me now,cheers all. – Sin5k4 Mar 21 '14 at 14:12

Kaf · Answer 2 · 2014-03-21T15:20:25.227

2

You don't need to use Convert() function:

To get 2 digits:

RIGHT(100 + Row_number () Over(Order By UH.Stuff), 2) AS HSN

3 digits:

RIGHT(1000 + Row_number () Over(Order By UH.Stuff), 3) AS HSN

So on ...

edited Mar 21 '14 at 15:20

answered Mar 21 '14 at 11:24

Kaf

33,101
7
58
78

score 1 · Answer 3 · answered Mar 21 '14 at 12:15

You can use this method to zero-pad numbers in SQL Server:

select right(1000000 + Row_number () Over(Order By UH.Stuff), 2) AS HSN

What this does is add a big number to your number. The function right() automatically converts it to a string. The right-most two digits will be zero-padded.

SQL Getting 2 digit numbers from row_number Over () function

3 Answers3