You must Sign In to post a response.
  • Category: Windows 8

    Sort Alphanumeric Column

    How to Sort a Column that Contains Character-Separated Numbers in sql?
    example- Input...
    1-1-1,
    1-10,
    2-1,
    2,
    2-1-3,
    1-1.
    Result Want to:
    1-1,
    1-1-1,
    1-10,
    2,
    2-1,
    2-1-3
  • #767418
    Hi,
    Use below script:
    USE [demo]
    GO
    CREATE TABLE [dbo].[Table_1](
    [password] [varchar](50) NULL
    ) ON [PRIMARY]
    GO
    INSERT [dbo].[Table_1] ([password]) VALUES (N'1-1-1')
    GO
    INSERT [dbo].[Table_1] ([password]) VALUES (N'1-10')
    GO
    INSERT [dbo].[Table_1] ([password]) VALUES (N'1-1')
    GO
    INSERT [dbo].[Table_1] ([password]) VALUES (N'2')
    GO
    INSERT [dbo].[Table_1] ([password]) VALUES (N'2-1-3')
    GO
    INSERT [dbo].[Table_1] ([password]) VALUES (N'2-1')
    GO
    SELECT password FROM dbo.Table_1 ORDER BY
    CASE WHEN PATINDEX('%[0-9]%',password) > 1 THEN
    LEFT(password,PATINDEX('%[0-9]%',password)-1)
    ELSE
    password
    END ,
    CASE WHEN PATINDEX('%[0-9]%',password) > 1 THEN
    CAST(SUBSTRING(password,PATINDEX('%[0-9]%',password),LEN(password)) as float)
    ELSE
    NULL
    END
    GO

  • #767419
    There is no direct way to do this. You can either sort by numeric or varchar. But you have to use some tricks to do that. Following is one of the trick that I am using in our application. You can try this, it will work for your requirement also.

    Select CName from(
    SELECT CName, CAST(replace([CName],'-','') AS INT) as CValue
    FROM YourTableName ) AS AA order by AA.CValue

    The above is simple logic. Just replace the '-" into blank then converting that into numeric then sorting. I am handling it as sub query. Try it.

    By Nathan
    Direction is important than speed

  • #767420
    Hai Ankush,
    You can smple replace the '-' with empty and then use Order By clause which will make the string as per your requirements.
    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #767427
    hello Shshi sir,
    If I add another value...i.e.
    INSERT [dbo].[Table_1] ([password]) VALUES (N'1-12-12')
    GO
    INSERT [dbo].[Table_1] ([password]) VALUES (N'1-12-2')
    GO


    output get wrong...

  • #767430
    Few more examples in below links,

    http://social.technet.microsoft.com/wiki/contents/articles/30527.t-sql-how-to-sort-a-column-that-contains-character-separated-numbers.aspx

    http://www.codeproject.com/Articles/842541/How-Do-I-Use-SQL-Server-to-Sort-Alphanumeric-Value


    Regards,
    Asheej T K
    Microsoft MVP[ASP.NET/IIS]
    DotNetSpider MVM


Sign In to post your comments