How to sort alphanumeric values in SQL Server properly

Question

I have a column with an alphanumeric value called TableName which could be almost any phrase as long as it is alphanumeric.

Sample data could be (Not necessarily 3 alphabet followed by a number)

AAA 1
AAB 2
AAC 3
AAB 10
AAC 12
AAB 12

If i use a normal sort like ORDER BY TableName

This will become

AAA 1
AAB 10
AAB 12
AAB 2
AAC 12
AAC 3

What I'm trying to accomplish is

AAA 1
AAB 2
AAB 10
AAB 12
AAC 3
AAC 12

I'd like you to know that this field COULD be ANYTHING i.e.

ABC123MAS3482
KASJ19LKA
213LKS23

Just to let you know that there is no format in the field. The only rule is that it is alpha numeric.

I hope you guys could help me with your advance knowledge in SQL.

I'm using SQL Server 2008 R2

Mike Laren · Accepted Answer · 2014-12-16 07:28:45Z

Sorting alphanumeric column in a different way:

CREATE TABLE dbo.AlphnumericTable (AlphnumericColumn varchar(50) NULL)

INSERT INTO dbo.AlphnumericTable (AlphnumericColumn) VALUES ('3')
INSERT INTO dbo.AlphnumericTable (AlphnumericColumn) VALUES ('AB1')
INSERT INTO dbo.AlphnumericTable (AlphnumericColumn) VALUES ('A1')
INSERT INTO dbo.AlphnumericTable (AlphnumericColumn) VALUES ('B2')
INSERT INTO dbo.AlphnumericTable (AlphnumericColumn) VALUES ('A11')
INSERT INTO dbo.AlphnumericTable (AlphnumericColumn) VALUES ('B20')
INSERT INTO dbo.AlphnumericTable (AlphnumericColumn) VALUES ('B21')
INSERT INTO dbo.AlphnumericTable (AlphnumericColumn) VALUES ('AB10')
INSERT INTO dbo.AlphnumericTable (AlphnumericColumn) VALUES ('B3')
INSERT INTO dbo.AlphnumericTable (AlphnumericColumn) VALUES ('AB100')
INSERT INTO dbo.AlphnumericTable (AlphnumericColumn) VALUES ('2')
INSERT INTO dbo.AlphnumericTable (AlphnumericColumn) VALUES ('1')
INSERT INTO dbo.AlphnumericTable (AlphnumericColumn) VALUES ('B32')
INSERT INTO dbo.AlphnumericTable (AlphnumericColumn) VALUES ('11')
SELECT AlphnumericColumn FROM dbo.AlphnumericTable
--Show normal Sort
SELECT AlphnumericColumn FROM dbo.AlphnumericTable ORDER BY AlphnumericColumn
--Show AlphaNumberic Sort
SELECT AlphnumericColumn FROM dbo.AlphnumericTable ORDER BY LEFT(AlphnumericColumn,PATINDEX('%[0-9]%',AlphnumericColumn)-1), -- alphabetical sort
CONVERT(INT,SUBSTRING(AlphnumericColumn,PATINDEX('%[0-9]%',AlphnumericColumn),LEN(AlphnumericColumn))) -- numerical sort
--cleanup our work
DROP Table dbo.AlphnumericTable

DrCopyPaste · Accepted Answer · 2014-01-17 10:19:30Z

I am pretty sure this cannot be done too easily.

Your statement I'd like you to know that this field COULD be ANYTHING i.e. pretty much kills it, otherwise one could have split alphabetic and numeric values in two columns or let them be split by some string-function.

But this way the only way out I could see is:

Write your own function that returns a numeric value by which you can sort. Then you can use it like this:

SELECT AlphaNumericValue FROM ValuesTable ORDER BY dbo.GETSORTVALUE(AlphaNumericValue)

This could be a dummy body for such a function:

CREATE FUNCTION dbo.GETSORTVALUE(@value NVARCHAR)
RETURNS INT
BEGIN
    DECLARE @dummyValue INT = LEN(@value)
    -- do something with your string here to get a sorting number
    RETURN @dummyValue
END

Since the values inside your column could be anything you would need to come up with a good sorting first, that should be included in that function then. For example you did not yet define how to sort in this case:

You say AAB 2 should come before AAB 10, but what about AA2B and AA10B?

Once you've defined this, your custom logic should go into that function.

Fitzroy · Accepted Answer · 2014-10-16 10:45:01Z

0

Write a function to insert a leading zero in front of each substring of numbers and sort by that. You might want to store that in a column and update whenever the data changes.

answered Oct 16, 2014 at 10:45

Fitzroy

31 silver badge4 bronze badges

Comments

Saeid · Accepted Answer · 2024-01-03 14:30:43Z

Create this function

create function dbo.udf_ExpandDigits(@src varchar(1024), @plen int, @letter 
char(1))
returns varchar(max)
 as 
begin
if @plen >= 100
  return @src;
declare @p int, @p2 int, @num varchar(100);
declare @ret_val varchar(max)='';
if (PATINDEX('%[0-9]%', @src) =0 )
  set @ret_val = @src;
else
begin
  set @p = patindex('%[0-9]%', @src);


  while(@p > 0)
  begin
     set @p2=patindex('%[^0-9]%', substring(@src, @p, 1000)) 
     if (@p2 > 0)
     begin
        set @num=substring(@src, @p, @p2-1);
        set @ret_val += left(@src, @p-1) + case when len(@num) < @plen then right(replicate(@letter, @plen) + @num, @plen) else @num end; ;
        set @src = substring(@src, @p+@p2-1, len(@src));
        set @p = patindex('%[0-9]%', @src);
     end
     else
     begin
        set @num = substring(@src, @p, len(@src));
        set @ret_val += left(@src, @p-1)+ case when len(@num) < @plen then right(replicate(@letter, @plen) + @num, @plen) else @num end;
        set @src ='';
        break;
     end

  end -- while (@p > 0)
  if len(@src) > 0
     set @ret_val += @src;
 end -- else
 return @ret_val;
end -- function

and use it like example:

SELECT 
  your_column ,
  Formatted_Code=dbo.udf_ExpandDigits(your_column, 3, '0')
        
 FROM 
    your_table
order by
    Formatted_Code

Collectives™ on Stack Overflow

How to sort alphanumeric values in SQL Server properly

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related