Alphanumeric sort on nvarchar(50) column

Question

I am trying to write a query that will return data sorted by an alphanumeric column, Code.

Below is my query:

  SELECT * 
  FROM <<TableName>>
    CROSS APPLY (SELECT PATINDEX('[A-Z, a-z][0-9]%', [Code]),
          CHARINDEX('', [Code]) ) ca(PatPos, SpacePos)
    CROSS APPLY (SELECT CONVERT(INTEGER, CASE WHEN ca.PatPos = 1 THEN 
          SUBSTRING([Code], 2,ISNULL(NULLIF(ca.SpacePos,0)-2, 8000)) ELSE NULL END),
   CASE WHEN ca.PatPos = 1 THEN LEFT([Code], 
          ISNULL(NULLIF(ca.SpacePos,0)-0,1)) ELSE [Code] END) ca2(OrderBy2, OrderBy1)
   WHERE [TypeID] = '1'

OUTPUT:

FFS1
FFS2
...
FFS12
FFS1.1
FFS1.2
...
FFS1.1E
FFS1.1R
...
FFS12.1
FFS12.2
FFS.12.1E
FFS12.1R
FFS12.2E
FFS12.2R

DESIRED OUTPUT:

FFS1
FFS1.1
FFS1.1E
FFS1.1R
....
FFS12
FFS12.1
FFS12.1E
FFS12.1R

What am I missing or overlooking?

EDIT:

Let me try to detail the table contents a little better. There are records for FFS1 - FFS12. Those are broken into X subs, i.e., FFS1.1 - FFS1.X to FFS12.1 - FFS12.X. The E and the R was not a typo, each sub record has two codes associated with it: FFS1.1E & FFS1.1R.

Additionally I tried using ORDER BY but it sorted as

FFS1 ... FFS10 FFS2

If you want query output sorted, you have to use `order by`... — HoneyBadger, Feb 12 '18 at 14:59
Your expected output doesn't really help for all the scenarios? Where should `'FFS2'` be? What about `'FFS.12.1E'` (which follows a different format to your other sample values)? — Thom A, Feb 12 '18 at 15:03
can you attach ddl of your table with insert script and expected output. — Pawan Kumar, Feb 12 '18 at 15:03
The reason you are struggling here is because your table design is not normalized. You are putting two pieces of information in a single tuple. This violates 1NF. Fix your design and the query is simple. — Sean Lange, Feb 12 '18 at 15:05
@SeanLange Based off this comment I am utilizing a sortorder column in my table to handle this. Query is cake now thanks! — R2B Boondocks, Feb 12 '18 at 16:47
That might work but the best way to fix this is to fix the data structure. But glad you figured out a solution. — Sean Lange, Feb 12 '18 at 16:49

Shnugo · Answer 1 · 2018-02-12T16:02:03.537

This will work for any count of parts separated by dots. The sorting is alphanumerical for each part separately.

DECLARE @YourValues TABLE(ID INT IDENTITY, SomeVal VARCHAR(100));
INSERT INTO @YourValues VALUES
 ('FFS1')
,('FFS2')
,('FFS12')
,('FFS1.1')
,('FFS1.2')
,('FFS1.1E')
,('FFS1.1R')
,('FFS12.1')
,('FFS12.2')
,('FFS.12.1E')
,('FFS12.1R')
,('FFS12.2E')
,('FFS12.2R');

--The query

WITH Splittable AS
(
    SELECT ID
          ,SomeVal
          ,CAST(N'<x>' + REPLACE(SomeVal,'.','</x><x>') + N'</x>' AS XML) AS Casted
    FROM @YourValues 
)
,Parted AS
(
    SELECT Splittable.*
          ,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS PartNmbr
          ,A.part.value(N'text()[1]','nvarchar(max)') AS Part
    FROM Splittable
    CROSS APPLY Splittable.Casted.nodes(N'/x') AS A(part)
)
,AddSortCrit AS
(
    SELECT ID 
          ,SomeVal
          ,(SELECT LEFT(x.Part + REPLICATE(' ',10),10) AS [*]
            FROM Parted AS x
            WHERE x.ID=Parted.ID
            ORDER BY PartNmbr
            FOR XML PATH('')
           ) AS SortColumn
    FROM Parted
    GROUP BY ID,SomeVal
)
SELECT ID
      ,SomeVal
FROM AddSortCrit 
ORDER BY SortColumn;

The result

ID  SomeVal
10  FFS.12.1E
1   FFS1
4   FFS1.1
6   FFS1.1E
7   FFS1.1R
5   FFS1.2
3   FFS12
8   FFS12.1
11  FFS12.1R
9   FFS12.2
12  FFS12.2E
13  FFS12.2R
2   FFS2

Some explanation:

The first CTE will transform your codes to XML, which allows to address each part separately.
The second CTE returns each part toegther with a number.
The third CTE re-concatenates your code, but each part is padded to a length of 10 characters.
The final SELECT uses this new single-string-per-row in the ORDER BY.

Final hint:

This design is bad! You should not store these values in concatenated strings... Store them in separate columns and fiddle them together just for the output/presentation layer. Doing so avoids this rather ugly fiddle...

Alphanumeric sort on nvarchar(50) column

1 Answers1

Some explanation:

Final hint: