You must Sign In to post a response.
  • Category: SQL Server

    Descending the Alphanumeric character in SQL Server 2008

    Hi

    While trying to descending the Alphanumeric characters (based on date in middle of the string) it shown wrongly. Below is the value and query. Actually the PONumber column were in Nvarchar in sql.

    Query:
    select * from PurchaseOrders order by
    CONVERT(NVARCHAR, PONumber, 118)+' '+CONVERT(NVARCHAR, PONumber, 118) desc

    Answer yield as:
    PO-12-27-2016-2016FMD073
    PO-12-22-2016-2016FMD072
    PO-12-16-2016-2016FMD071
    PO-12-14-2016-2016FMD070
    PO-12-06-2016-2016FMD069
    PO-11-29-2016-2016FMD068
    PO-02-16-2017-2017FMD012
    PO-01-27-2017-2017FMD008
    PO-01-13-2017-2017FMD005
    PO-01-05-2017-2017FMD002

    Actual required answer is:
    PO-11-29-2016-2016FMD068
    PO-12-06-2016-2016FMD069
    PO-12-14-2016-2016FMD070
    PO-12-16-2016-2016FMD071
    PO-12-22-2016-2016FMD072
    PO-12-27-2016-2016FMD073
    PO-01-05-2017-2017FMD002
    PO-01-13-2017-2017FMD005
    PO-01-27-2017-2017FMD008
    PO-02-16-2017-2017FMD012

    How can i process this..
  • #768969
    You can use this code snippet in your source to to Sort Alphanumeric Data in SQL
     
    SELECT col1
    FROM @t
    ORDER BY LEFT(col1,PATINDEX('%[0-9]%',col1)-1), -- Alphabetical sort
    CONVERT(INT,SUBSTRING(col1,PATINDEX('%[0-9]%',col1),LEN(col1))) -- Numerical sort

    Or

    SELECT [ScheduleID],ScheduleName
    FROM [Schedule]
    ORDER BY schedulename ASC

    Useful reference :
    https://www.codeproject.com/Articles/51602/How-to-Sort-Alphanumeric-Data-in-SQL

  • #768972
    Hi Phagu,

    Even, your query is not yield as required order. I have made it by referring as per link provided by you. Below query is working fine. As i split the date from the PONumber and ascending this date.... it's provide me the exact sort order....

    Select *, CONVERT(date,(LEFT(RIGHT(PONumber, len(PONumber)-3),len(PONumber)-14)),101) as aaaa
    FROM PurchaseOrders
    Order by aaaa asc


Sign In to post your comments