Problem in sorting by alphanumeric table column of NVARCHAR datatype.

Question

Post reply

Problem in sorting by alphanumeric table column of NVARCHAR datatype.

VSSGeorge

SSCrazy Eights

Points: 8151
More actions
September 24, 2015 at 4:59 am

#311776

I am trying to sort my sql resultset by an alphanumeric column of a table which is of NVARCHAR datatype.
The sample data is given below:
CREATE TABLE #Activities(activityName NVARCHAR(100))
INSERT INTO #Activities VALUES('Field phase S14-04932-01')
INSERT INTO #Activities VALUES('Phase reporting')
INSERT INTO #Activities VALUES('Phase running')
INSERT INTO #Activities VALUES('RD1')
INSERT INTO #Activities VALUES('A1')
INSERT INTO #Activities VALUES('A2')
INSERT INTO #Activities VALUES('A3')
INSERT INTO #Activities VALUES('A4')
INSERT INTO #Activities VALUES('E1 0DAA1')
INSERT INTO #Activities VALUES('E2 0DAA2')
INSERT INTO #Activities VALUES('E4 0DAA3')
INSERT INTO #Activities VALUES('E4 21+-7DAA2')
INSERT INTO #Activities VALUES('E5 0DAA4')
INSERT INTO #Activities VALUES('E6')
INSERT INTO #Activities VALUES('E7')
INSERT INTO #Activities VALUES('E8')
INSERT INTO #Activities VALUES('E9')
INSERT INTO #Activities VALUES('E10')
SELECT activityName
FROM #Activities
ORDER BY
case when ISNUMERIC(activityName)<>1 then activityName else '0' end,
case when ISNUMERIC(activityName) =1 then CONVERT(INT, activityName) else -1 end
The output of the query is like this:
A1
A2
A3
A4
E1 0DAA1
E10
E2 0DAA2
E4 0DAA3
E4 21+-7DAA2
E5 0DAA4
E6
E7
E8
E9
Field phase S14-04932-01
Phase reporting
Phase running
RD1
The output what I require is this:
A1
A2
A3
A4
E1 0DAA1
E2 0DAA2
E4 0DAA3
E4 21+-7DAA2
E5 0DAA4
E6
E7
E8
E9
E10
Field phase S14-04932-01
Phase reporting
Phase running
RD1

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Something like this?

SELECT activityName

FROM #Activities

ORDER BY

Case When IsNumeric(activityName) = 1 then Right(Replicate('0',21) + activityName, 20)

When IsNumeric(activityName) = 0 then Left(activityName + Replicate('',21), 20)

Else activityName

End

VSSGeorge SSCrazy Eights Points: 8151 More actions · Answer 2

Hi anthony.green,

The script you posted also fetches the same result. E10 is coming just below E1 0DAA1. In the required output, it should come below E9.

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

Whoops, oversight on my part I do appologise.

http://technologycraftsmen.net/blog/2010/10/19/alphanumeric-sorting-in-mssql/ has a function which will do it

SELECT activityName

FROM #Activities

ORDER BY

master.[dbo].[fn_CreateAlphanumericSortValue] (activityName,1)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

SELECT activityName, SortOrder = x4.LeftChars + x6.NewRightNums + ' ' + x3.RightPart

FROM #Activities

CROSS APPLY (SELECT SpacePos = CHARINDEX(' ',activityName+' ')) x1

CROSS APPLY (SELECT LeftPart = LEFT(activityName,x1.SpacePos-1)) x2

CROSS APPLY (SELECT RightPart = SUBSTRING(activityName,x1.SpacePos,8000)) x3

CROSS APPLY (SELECT LeftChars =

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(x2.LeftPart,'9',''),'8',''),'7',''),'6',''),'5',''),'4',''),'3',''),'2',''),'1',''),'0','')

) x4

CROSS APPLY (SELECT RightNumbs =

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

x2.LeftPart,'A',''),'B',''),'C',''),'D',''),'E',''),'F',''),'G',''),'H',''),'I',''),'J',''),'K',''),'L',''),'M',''),'N',''),'O',''),'P',''),'Q',''),'R',''),'S',''),'T',''),'U',''),'V',''),'W',''),'X',''),'Y',''),'Z','')

) x5

CROSS APPLY (

SELECT NewRightNums = CASE WHEN x5.RightNumbs > '' THEN RIGHT('00'+x5.RightNumbs,3) ELSE '' END

) x6

ORDER BY SortOrder

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 5

If the first word contains digit then sort by first word parts second part considered to be integer. Otherwise sort by hole column

select activityName

from #Activities

cross apply (select

blank = charindex(' ',activityName)

,digit = patindex('%[0123456789]%', case charindex(' ',activityName)

when 0 then activityName

else left(activityName, charindex(' ',activityName) -1) end) ) i

order by

Case i.digit when 0 then activityName

else left(activityName, i.digit-1) end

, case i.digit when 0 then null

else cast(substring (activityName, i.digit, case i.blank when 0 then 8000 else i.blank-1 end) as int) end

May need to check second part of the first word if it is numeric and .. What if it is not?

WayneS SSC Guru Points: 95460 More actions · Answer 6

SELECT activityName

FROM #Activities

CROSS APPLY (SELECT PATINDEX('[A-Z,a-z][0-9]%', activityName),

CHARINDEX(' ', activityName) ) ca(PatPos, SpacePos)

CROSS APPLY (SELECT CONVERT(INTEGER, CASE WHEN ca.PatPos = 1 THEN SUBSTRING(activityName, 2, ISNULL(NULLIF(ca.SpacePos,0)-2, 8000)) ELSE NULL END),

CASE WHEN ca.PatPos = 1 THEN LEFT(activityName, ISNULL(NULLIF(ca.SpacePos,0)-2, 1)) ELSE activityName END) ca2(OrderBy2, OrderBy1)

ORDER BY ca2.OrderBy1, ca2.OrderBy2;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

WayneS (9/24/2015)
SELECT activityName FROM #Activities CROSS APPLY (SELECT PATINDEX('[A-Z,a-z][0-9]%', activityName), CHARINDEX(' ', activityName) ) ca(PatPos, SpacePos) CROSS APPLY (SELECT CONVERT(INTEGER, CASE WHEN ca.PatPos = 1 THEN SUBSTRING(activityName, 2, ISNULL(NULLIF(ca.SpacePos,0)-2, 8000)) ELSE NULL END), CASE WHEN ca.PatPos = 1 THEN LEFT(activityName, ISNULL(NULLIF(ca.SpacePos,0)-2, 1)) ELSE activityName END) ca2(OrderBy2, OrderBy1)
ORDER BY ca2.OrderBy1, ca2.OrderBy2;

Nice job, Wayne 🙂

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

WayneS SSC Guru Points: 95460 More actions · Answer 8

ChrisM@Work (9/24/2015)
WayneS (9/24/2015)
SELECT activityName FROM #Activities CROSS APPLY (SELECT PATINDEX('[A-Z,a-z][0-9]%', activityName), CHARINDEX(' ', activityName) ) ca(PatPos, SpacePos) CROSS APPLY (SELECT CONVERT(INTEGER, CASE WHEN ca.PatPos = 1 THEN SUBSTRING(activityName, 2, ISNULL(NULLIF(ca.SpacePos,0)-2, 8000)) ELSE NULL END), CASE WHEN ca.PatPos = 1 THEN LEFT(activityName, ISNULL(NULLIF(ca.SpacePos,0)-2, 1)) ELSE activityName END) ca2(OrderBy2, OrderBy1)
ORDER BY ca2.OrderBy1, ca2.OrderBy2;
Nice job, Wayne 🙂

Thanks.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 9

WayneS (9/24/2015)
... PATINDEX('[A-Z,a-z][0-9]%', activityName ...

CREATE TABLE #Activities(activityName NVARCHAR(100))

?