Clever Geek Handbook

How do I increment and format an integer in a result table in SQL Server 2008?

I read a lot of posts related to this problem, but unfortunately none of the suggested solutions worked for me.

I am trying to create a SQL statement that will create a column in my results table with values ​​formatted like this:

date + 8-digit number, incremented by 1 for each new entry and prefix with leading zeros.

In other words, I would like to have a data column in the following format:

    2015061000000001
    2015061000000002
    2015061000000003
    2015061000000004
    2015061000000005
    ...

I tried using row_number()

as well as local variable, but I was not very lucky it worked.

This is what I have so far:

    declare @a int;
    set @a = 1;

    select 
        'aaa',                          
        (select (CONVERT(VARCHAR(10),GETDATE(),112)) + RIGHT('0000000'+ CONVERT(VARCHAR,2),8)),
        row_number() over(order by id), 
        (select (CONVERT(VARCHAR(10),GETDATE(),112)) + RIGHT('0000000'+ CONVERT(VARCHAR, ( row_number() over(order by id) )),8)),
        (select (CONVERT(VARCHAR(10),GETDATE(),112)) + RIGHT('0000000'+ CONVERT(VARCHAR, ( select @a + 1 )),8))
    FROM MY_TABLE

Results table:

    aaa | 2015061000000002 | 1 | 2015061000000001 | 2015061000000002
    aaa | 2015061000000002 | 2 | 2015061000000001 | 2015061000000002
    aaa | 2015061000000002 | 3 | 2015061000000001 | 2015061000000002
    aaa | 2015061000000002 | 4 | 2015061000000001 | 2015061000000002
    aaa | 2015061000000002 | 5 | 2015061000000001 | 2015061000000002
    aaa | 2015061000000002 | 6 | 2015061000000001 | 2015061000000002
    aaa | 2015061000000002 | 7 | 2015061000000001 | 2015061000000002
    aaa | 2015061000000002 | 8 | 2015061000000001 | 2015061000000002

Can anyone advise on how to do this?

    2015061000000001
    2015061000000002
    2015061000000003
    2015061000000004
    2015061000000005
    ...

Thank!

+3


source to share


3 answers


You are on the right track, just need a little push ...
Try this:



SELECT CONVERT(varchar(10), GETDATE(), 112) + 
       RIGHT('0000000'+ 
       CAST( ROW_NUMBER() OVER(ORDER BY t_Id) As varchar(8))
       ,8)
FROM MY_TABLE
+6


source


Try the following:

  SELECT
        'aaa',    
        CONVERT(VARCHAR(10),GETDATE(),112) + 
        RIGHT('0000000' + CAST(ROW_NUMBER() OVER (ORDER BY id) AS VARCHAR(8)), 8)            
  FROM yourTable


Combine the result with ROW_NUMBER

7 '0'

to make sure you always have at least 8 digits, and then select exactly 8 starting from the rightmost digit.

+5


source


Or even a stranger:

    , CONVERT( nvarchar(20), CAST(CONVERT(nvarchar(10), GETDATE(), 112) AS decimal(20,0)) * POWER( 10,8) + ROW_NUMBER() OVER (order by id))
+3


source






More articles:


All Articles