Reputation: 215

Sorting VARCHAR column with alphanumeric entries

I am using SQL Server, the column is a VARCHAR(50) and I want to sort it like this:

What I have so far is:

Select *
From viewASD
ORDER BY 
    Case When IsNumeric(LEFT(asdNumNew,1)) = 1 
         Then CASE When IsNumeric(asdNumNew) = 1 
                   Then Right(Replicate('0',20) + asdNumNew + '0', 20)
                   Else Right(Replicate('0',20) + asdNumNew, 20) 
              END
         When IsNumeric(LEFT(asdNumNew,1)) = 0 
         Then Left(asdNumNew + Replicate('',21), 20)
    End

But this SQL statement puts '14 Draft' right after '26'.

Could someone help? Thanks

Upvotes: 6

Answers (5)

ascendedcrow

Reputation: 53

What worked for me is I split up the numeric and the alpha parts and then sorted based on the Alpha, then the Numeric:

CREATE FUNCTION [admin].[GetUnitNumberAsIntFunc](@UnitNumber varchar(20))
RETURNS int
BEGIN
    DECLARE @intPosition int 
    SET @intPosition = PATINDEX('%[^0-9]%', @UnitNumber)

    WHILE @intNumber > 0
    BEGIN 
        SET @UnitNumber = STUFF(@UnitNumber, @intNumber, 1, '')
        SET @intPosition = PATINDEX('%[^0-9]%', @UnitNumber)
    END 

    RETURN ISNULL(@UnitNumber,9999) 
    
END;

CREATE FUNCTION [admin].[GetUnitNumberAsStrFunc](@UnitNumber varchar(20))
RETURNS varchar(20)
BEGIN
    DECLARE @intPosition int 
    SET @intPosition = PATINDEX('%[0-9]%', @UnitNumber)

    SET @UnitNumber = STUFF(@UnitNumber, @intPosition, 6, '')

    RETURN ISNULL(@UnitNumber,9999) 
    
END;

Upvotes: 0

user7011379

Reputation:

TRY THIS

DECLARE @t table (Number nvarchar(20)) 
INSERT INTO @t 
SELECT           'L010' 
UNION ALL SELECT 'L011' 
UNION ALL SELECT 'L011' 
UNION ALL SELECT 'L001' 
UNION ALL SELECT 'L012' 
UNION ALL SELECT '18'  
UNION ALL SELECT '8' 
UNION ALL SELECT '17' 
UNION ALL SELECT 'B004'    
UNION ALL SELECT 'B006'    
UNION ALL SELECT 'B008'
UNION ALL SELECT 'B018'   
UNION ALL SELECT 'UG001'
UNION ALL SELECT 'UG011'   
UNION ALL SELECT 'G001'    
UNION ALL SELECT  'G002' 
UNION ALL SELECT 'G011';
    
SELECT Number 
FROM @t 
ORDER BY 
CAST
(
    SUBSTRING
    (
        Number
        , 1
        , CASE 
            WHEN patindex('%[^0-9]%',Number) > 0 THEN patindex('%[^0-9]%',Number) - 1 
            ELSE LEN(Number) END
    ) AS int
)
, Number

Upvotes: 0

Robert Sievers

Reputation: 1355

I had something similar, but with the possibility of dashes as leading characters as well as trailing spaces. This code worked for me.

SELECT 
    my_column,
    PATINDEX('%[^0-9]%',my_column) AS first_alpha_position,
    CONVERT(INT,
    CASE 
        WHEN PATINDEX('%[^0-9]%',my_column) = 0 OR PATINDEX('-%',my_column) = 1
            THEN ABS(my_column)
        ELSE SUBSTRING(my_column,1,PATINDEX('%[^0-9]%',my_column) -1)
    END) AS numeric_value,
    LTRIM(
        SUBSTRING(my_column,PATINDEX('%[^0-9]%',my_column),LEN(my_column)-PATINDEX('%[^0-9]%',my_column)+1)
) AS alpha_chars
FROM my_table
ORDER BY numeric_value,alpha_chars

Upvotes: 0

Dmitrii Bychenko

Reputation: 186668

If all numbers within the string are reasonably small, say not exceeding 10 digits, you may expand all the numbers in the string to be exactly 10 digits:

123A -> 0000000123A

 S4 -> S0000000004

A3B89 -> A0000000003B0000000089

and so on and then sort them

-- Expand all numbers within S by zeros to be MaxLen
create function [dbo].ExpandNumbers(@S VarChar(4000), @maxlen integer) returns VarChar(4000)
as
begin
  declare @result VarChar(4000);
  declare @buffer VarChar(4000);
  declare @Ch Char;

  declare @i integer;

  set @buffer = '';
  set @result = '';
  set @i = 1;

  while (@i <= len(@S))
    begin
      set @Ch = substring(@S, @i, 1);


      if ((@Ch >= '0') and (@Ch <= '9')) 
        set @buffer = @buffer + @Ch
      else 
        begin
          if (len(@buffer) > 0) 
            set @result = @result + right(replicate('0', @maxlen) + @buffer, @maxlen);

          set @buffer = '';  
          set @result = @result + @Ch;
        end;

      set @i = @i + 1;  
    end;

  if (len(@buffer) > 0) 
    set @result = @result + right(replicate('0', @maxlen) + @buffer, @maxlen);

  return @result;
end;

-- Final query is

   select *
    from viewASD
order by [dbo].ExpandNumbers(asdNumNew)

Upvotes: 0

DougM

Reputation: 2888

Your WHERE statement is... oddly complex.

It looks like you want to sort by any leading numeric digits in integer order, and then sort by the remainder. If so, you should do that as separate clauses, rather than trying to do it all in one. The specific issue you're having is that you're only allowing for a single-digit number, instead of two or more. (And there's No such thing as two.)

Here's your fix, along with a SQLFiddle, using two separate calculated columns tests for your ORDER BY. (Note that this assumes the numeric portion of asdNumNew will fit in a T-SQL int. If not, you'll need to adjust the CAST and the maximum value on the first ELSE.)

SELECT * FROM viewASD
ORDER BY 
CASE 
  WHEN ISNUMERIC(asdNumNew)=1 
  THEN CAST(asdNumNew as int)

  WHEN PATINDEX('%[^0-9]%',asdNumNew) > 1 
  THEN CAST(
    LEFT(
      asdNumNew,
      PATINDEX('%[^0-9]%',asdNumNew) - 1
    ) as int)

  ELSE 2147483648
END, 


CASE 
  WHEN ISNUMERIC(asdNumNew)=1 
  THEN NULL

  WHEN PATINDEX('%[^0-9]%',asdNumNew) > 1 
  THEN SUBSTRING(
      asdNumNew,
      PATINDEX('%[^0-9]%',asdNumNew) ,
      50
    ) 

  ELSE asdNumNew
END

Upvotes: 4

Sorting VARCHAR column with alphanumeric entries

Answers (5)

Related Questions