JamshaidRiaz JamshaidRiaz - 3 months ago 10
SQL Question

Generating Number on the base of Alphabetic Order

I am trying to write a Store Procedure in sql-server-2008, which should generate ID number on the basis of another Field's first letter like below:

Auto Generation of ID alphabetically

I am using sql-server-2008. Would be grateful on any help. Regards

Answer

So A's are all 1XXXX, B's 2XXXX through Z's 26XXXX. Try this:

SELECT
        10000 * ( ASCII(LEFT(Field2, 1)) - 64 )
        + ROW_NUMBER() OVER ( PARTITION BY LEFT(Field2, 1) ORDER BY Field2 ) Field1
        ,Field2
    FROM
        ( VALUES ( 'Alpha Company'), ( 'Airborn Company'), ( 'Bravo Company'),
        ( 'Charlie Company'), ( 'Cake Company'), ( 'Camel Company'),
        ( 'Delta Company'), ( 'Zeta Company') ) A ( Field2 ); 


Field1               Field2
-------------------- ---------------
10001                Airborn Company
10002                Alpha Company
20001                Bravo Company
30001                Cake Company
30002                Camel Company
30003                Charlie Company
40001                Delta Company
260001               Zeta Company
Comments