SQL Server Performance

Sort nvarchar field to show numeric order

Discussion in 'Performance Tuning for DBAs' started by PAMUR, Mar 7, 2007.

  1. PAMUR New Member

    Hi all,

    In a DB I have found an nvarchar code field which needs to be sorted as follows:

    1,2,3,4,5,6,10,100,200,2000,A1,A2,AB1,AB2,ABC1,ABC2

    Is there a way to do this?

    Thank you.

    Usha Rani
    PAMUR, Mar 7, 2007
    #2

  2. FrankKalis Moderator

    I suspect that you have more tricky data in that column, but if there really is only data like in your sample data, this should work:


    CREATE TABLE #natsort
    (
    col1 NVARCHAR(10)
    )
    INSERT INTO #natsort
    SELECT '1'
    UNION ALL
    SELECT 'ABC12'
    UNION ALL
    SELECT '2'
    UNION ALL
    SELECT 'A10'
    UNION ALL
    SELECT '0'
    UNION ALL
    SELECT 'Z1000'

    SELECT *
    FROM #natsort
    ORDER BY LEFT(col1, LEN(col1)- PATINDEX('%[a-Z]%', col1))
    , LEFT(col1, LEN(col1)- PATINDEX('%[0-9]%', col1))

    DROP TABLE #natsort

    col1
    ----------
    0
    1
    2
    A10
    ABC12
    Z1000

    (6 row(s) affected)

    Anyway, most client languages have more efficient algorithms for sorting data like this. For example, that function in PHP is called natsort(). I would consider doing this natural sorting at the client.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    FrankKalis, Mar 8, 2007
    #2

  3. PAMUR New Member

    Hi Frank,

    I did it but alas! it shows hte order as
    0,1,10,100,2,A1, ABC12

    How else to do it?



    oops! Thank you Frank for natsort() info. Looking at it. Will try that.

    Usha Rani

    Usha Rani
    PAMUR, Mar 8, 2007
    #2

  4. FrankKalis Moderator

    Sorry for that.

    Will this work?


    SELECT *
    FROM #natsort
    ORDER BY CAST(RIGHT(col1, LEN(col1) - PATINDEX('%[0-9]%', col1)+1) AS INT)
    , LEFT(col1, PATINDEX('%[0-9]%', col1)-1)

    Note, that it will fail, when you have data like 'A' (a single character) in your table.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    FrankKalis, Mar 8, 2007
    #2

  5. PAMUR New Member

    Thank you! Frank. But still the three digit numaral does not fall under numeric order.

    All Numerals must come first. Then the alphanumeric.

    Probably we got to sort all numeral and place on top of list, then seperate numeric part and alphanumeric part from alpanumeric, Sort them and join back some thing of that sort.

    Hmmm...



    Trying it.

    Thank you.

    Usha Rani
    PAMUR, Mar 8, 2007
    #2

  6. FrankKalis Moderator

    Stupid me. [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />Sometimes I don't see the wood for the trees. This *should* be better:<br /><pre id="code"><font face="courier" size="2" id="code"><br />CREATE TABLE #natsort<br />(<br /> col1 NVARCHAR(10)<br />)<br />INSERT INTO #natsort<br /> SELECT '1'<br />UNION ALL<br /> SELECT 'ABC12'<br />UNION ALL<br /> SELECT '2'<br />UNION ALL <br /> SELECT 'A1'<br />UNION ALL <br /> SELECT '0'<br />UNION ALL <br /> SELECT '10'<br />UNION ALL <br /> SELECT 'A10'<br />UNION ALL <br /> SELECT 'AB1'<br />UNION ALL <br /> SELECT 'Z1000'<br /><br />SELECT *<br /> FROM #natsort<br /> ORDER BY RIGHT('0000000000'+LTRIM(col1),10) <br /><br />DROP TABLE #natsort<br /><br />col1<br />----------<br />0<br />1<br />2<br />10<br />A1<br />A10<br />AB1<br />ABC12<br />Z1000<br /><br />(9 row(s) affected)<br /></font id="code"></pre id="code"><br />Sorry! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Mar 8, 2007
    #2

  7. PAMUR New Member

    Hmmmm...... You are very Cleaver! Thank you. Been a lot insight fulll.<br />How come that 100 is after A2? <img src='/community/emoticons/emotion-6.gif' alt=':(' /><br /><br />Running your query<br />the result set is:<br />1<br />2<br />3<br />5<br />10<br />A1<br />A2<br />100<br />AB1<br />AB2<br />AC1<br />AC2<br />B10<br />B11<br />BA1<br />BC1<br />A100<br />A200<br />ABC1<br />ABC2<br />B100<br />BCA1<br /><br /><br />Usha Rani
    PAMUR, Mar 8, 2007
    #2

  8. PAMUR New Member

    Hi Frank,

    I got it!

    Select SortID, substring(SortID,1,len(SortID) - Len(RIGHT(SortID, LEN(SortID) - PATINDEX('%[0-9]%', SortID)+1)))alpha,
    CAST(RIGHT(SortID, LEN(SortID) - PATINDEX('%[0-9]%', SortID)+1) AS INT)intv
    From SortNvarcharField
    Order BY alpha, intv


    This works for the data of:
    1,3,5,2,0,10,100,20,2000, A1, AB12,A123,.........

    But is there a better way to perform well?



    Usha Rani
    PAMUR, Mar 8, 2007
    #2

  9. FrankKalis Moderator

    Dang! You got me once again. Guess, that isn't my day today. [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />No, actually I don't think, any server solution will perform well. T-SQL isn't the language of choice for this sort of string manipulation. Probably the "best" suggestion here would be to design your table properly and split the data here from 1 into 2 columns, if you're in the position to control the tables' structure. You're mixing data from different domains and that's the root cause for that more or less ugly workaround, we've finally figured out here. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Mar 8, 2007
    #2

  10. PAMUR New Member

    Thank you Frank.

    I have suggested the change to my PM but well they have their limitations. But if I design the table or reengineer it I will definitely
    follow your advice.

    My sincere thanks once again.


    Usha Rani
    PAMUR, Mar 9, 2007
    #2

  11. PAMUR New Member

    HI Frank,<br /><br />This is a real junk. Well it works now but.........<br />really not the solution<br />[<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />Select SortID, <br />Case When PATINDEX('%[0-9]%', SortID) = 0<br /> Then SortID <br /> Else Substring(SortID,1,len(SortID) - Len(RIGHT(SortID, LEN(SortID) - PATINDEX('%[0-9]%', SortID)+1))) End as vAlpha,<br /><br />Case When PATINDEX('%[0-9]%', SortID) &lt;&gt; 0<br />Then CAST(RIGHT(SortID, LEN(SortID) - PATINDEX('%[0-9]%', SortID)+1) AS INT)<br />Else null END as vInt <br />From SortNvarcharField<br />Order BY vAlpha, vInt<br /><br />1<br />2<br />3<br />5<br />10<br />100<br />A<br />A1<br />A2<br />A100<br />A200<br />AA<br />AB<br />AB1<br />AB2<br />ABC<br />ABC1<br />ABC2<br />AC1<br />AC2<br />B<br />B10<br />B11<br />B100<br />BA<br />BA1<br />BB<br />BBA<br />BBC<br />BC<br />BC1<br />BCA1<br />BD<br /><br /><br />Usha Rani
    PAMUR, Mar 11, 2007
    #2

  12. khtan New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by PAMUR</i><br /><br />HI Frank,<br /><br />This is a real junk.<br /><br />[<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br /><br />Usha Rani<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />What do you mean by 'junk' ? The code is not working at all ?<br /><br /><hr noshade size="1"><br /><font color="blue"><font size="1"><i>KH</i></font id="size1"></font id="blue">
    khtan, Mar 11, 2007
    #2

  13. PAMUR New Member

    Well it works. I mean to say it is't a solution to follow or advice. Frank is right but how else to takle with problems of this sort without being able to change the design?

    Thank you.

    Usha Rani
    PAMUR, Mar 11, 2007
    #2

Share This Page