Category: SQL Server

28 Feb 2017 18:38

Joined:	18/12/2009
Level:	Silver
Points:	104

Points:	5

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

28 Feb 2017 20:26

Joined:	30/04/2012
Level:	Gold
Points:	7233

Points:	3

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


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

02 Mar 2017 13:03

Joined:	18/12/2009
Level:	Silver
Points:	104

Points:	1

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

Descending the Alphanumeric character in SQL Server 2008

About dotnetspider.com

Quick Links

General

Services