none
How to run select query to display column values in correct asc order, if values have letters as lead RRS feed

  • Question

  • <g class="gr_ gr_54 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" data-gr-id="54" id="54">Hi</g> I am very new to SQL and I have a query where I need to list values of columns in <g class="gr_ gr_50 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="50" id="50">asc</g> order.

    Eg: 

    121-P1

    121-P2

    121-P3

    .

    .

    .

    121-P10

    121-P11

    However, I get the list as below:

    121-P1

    121-P10

    121-P11

    .

    .

    121-P16

    121-P2

    121-P3

    How do I list it in <g class="gr_ gr_468 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" data-gr-id="468" id="468">correct</g> order?

    Wednesday, July 12, 2017 12:14 AM

All replies

  • Hi ojoc,

    You just need to modify your ORDER BY clause a little bit, here’s an example:
    CREATE TABLE [dbo].[TEST] ---CREATE TEST TABLE
    (
        [C1] [VARCHAR](50) NULL
    ) ON [PRIMARY]
    GO
    
    --Insert data to table 
    
    --
    
    SELECT C1
    FROM dbo.TEST
    ORDER BY CAST(SUBSTRING(C1, CHARINDEX('P',C1)+1,LEN(C1)-CHARINDEX('P',C1)) AS INT) --Add this order by clause to yout query

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, July 12, 2017 8:21 AM