SQL Server 2008 - order by strings with number numerically

Question

I have following values in my table:

ABC
ABC1
ABC2
ABC3 and so on...

ABC11
ABC12
ABC13 and so on..

ABC20
ABC21
ABC22 and so on..

So basically what I have is any string value (not always ABC, any string value) that can either be followed by the number or it may just be a string without the number.

When I do select * from table order by my column asc I get following results:

ABC
ABC1
ABC11
ABC12
ABC13
ABC2
ABC20
ABC21
ABC22
ABC3
ABC31
ABC32

I need it sorted numerically:

ABC
ABC1
ABC2
ABC3
ABC11
ABC12
ABC13
ABC20
ABC21
ABC22
ABC31
ABC32

How can this be accomplished?

Answer 1

Here's a simple to understand example for those using SQL Server 17+.

DECLARE @Data table ( val varchar(10) );
INSERT INTO @Data VALUES
    ( 'ABC' ),( 'ABC1' ),( 'ABC11' ),( 'ABC12' ),( 'ABC13' ),( 'ABC2' ), ( 'B1C' ),
    ( 'ABC20' ),( 'ABC21' ),( 'ABC22' ),( 'ABC3' ),( 'ABC31' ),( 'ABC32' );

SELECT val FROM @Data AS d
CROSS APPLY (
    SELECT CAST ( TRANSLATE ( d.val, 'abcdefghijklmnopqrstuvwxyz', '                          ' ) AS int ) AS Num
) AS x
ORDER BY
    LEFT ( val, 1 ), Num;

Returns

+-------+
|  val  |
+-------+
| ABC   |
| ABC1  |
| ABC2  |
| ABC3  |
| ABC11 |
| ABC12 |
| ABC13 |
| ABC20 |
| ABC21 |
| ABC22 |
| ABC31 |
| ABC32 |
| B1C   |
+-------+

SQL Server's TRANSLATE takes three parameters: inputString, characters, translations.

The inputString in your case is your column name.

The characters are the values you're looking to replace, in this case the alphabet.

The translations are the values to replace with. This string must be equal in length to the characters--hence the empty string that's 26 spaces long.

And finally, using CAST ignores the spaces and allows the remaining value to be sorted as an int.

You can read about TRANSLATE here:

https://learn.microsoft.com/en-us/sql/t-sql/functions/translate-transact-sql?view=sql-server-ver15

Answer 2

Using Standard SQL ONLY this query demonstrates how you can ODER BY either the numbers found to be contained at the Beginning or the End of the string. The query also shows how you could then look at the remaining 'inner' portion of the string to see if there are any numbers contained there. Knowing if there are numbers contained within the string could be useful if further processing is necessary.

WITH stringNumberData AS
(   /* Build up Fake data with Numbers at the Beginning, End and Middle of the string */
    SELECT 1  AS uniqueKey, 'ABC'      AS NumberFromString UNION ALL
    SELECT 2  AS uniqueKey, 'ABC1'     AS NumberFromString UNION ALL
    SELECT 3  AS uniqueKey, 'ABC2'     AS NumberFromString UNION ALL
    SELECT 4  AS uniqueKey, 'ABC3'     AS NumberFromString UNION ALL
    SELECT 5  AS uniqueKey, 'ABC10'    AS NumberFromString UNION ALL
    SELECT 6  AS uniqueKey, 'ABC11'    AS NumberFromString UNION ALL
    SELECT 7  AS uniqueKey, 'ABC12'    AS NumberFromString UNION ALL
    SELECT 8  AS uniqueKey, 'ABC20'    AS NumberFromString UNION ALL
    SELECT 9  AS uniqueKey, 'ABC21'    AS NumberFromString UNION ALL
    SELECT 10 AS uniqueKey, 'ABC22'    AS NumberFromString UNION ALL
    SELECT 11 AS uniqueKey, 'ABC30'    AS NumberFromString UNION ALL
    SELECT 12 AS uniqueKey, 'ABC31'    AS NumberFromString UNION ALL
    SELECT 13 AS uniqueKey, 'ABC32'    AS NumberFromString UNION ALL
    SELECT 14 AS uniqueKey, '1ABC'     AS NumberFromString UNION ALL
    SELECT 15 AS uniqueKey, '2ABC'     AS NumberFromString UNION ALL
    SELECT 16 AS uniqueKey, '3ABC'     AS NumberFromString UNION ALL
    SELECT 17 AS uniqueKey, '10ABC'    AS NumberFromString UNION ALL
    SELECT 18 AS uniqueKey, '11BC'     AS NumberFromString UNION ALL
    SELECT 19 AS uniqueKey, '12ABC'    AS NumberFromString UNION ALL
    SELECT 20 AS uniqueKey, '10ABC18'  AS NumberFromString UNION ALL
    SELECT 21 AS uniqueKey, '11BC52'   AS NumberFromString UNION ALL
    SELECT 22 AS uniqueKey, '12ABC42'  AS NumberFromString UNION ALL
    SELECT 23 AS uniqueKey, 'A3BC18'   AS NumberFromString UNION ALL
    SELECT 24 AS uniqueKey, 'B3C52'    AS NumberFromString UNION ALL
    SELECT 25 AS uniqueKey, '12AB3C'   AS NumberFromString UNION ALL
    SELECT 26 AS uniqueKey, 'A3BC'     AS NumberFromString UNION ALL
    SELECT 27 AS uniqueKey, 'AB2C'     AS NumberFromString UNION ALL
    SELECT 28 AS uniqueKey, 'ABC85D'   AS NumberFromString

)   

SELECT d.uniqueKey, d.NumberFromString
    /* Extract numerical values contained on the LEFT of the String by finding the index of the first non number */
    , LEFT(d.NumberFromString, PATINDEX('%[^0-9]%', d.NumberFromString) -1) AS 'Left Numbers Extraction'

    /* Extract numerical data contained on the RIGHT of the String */
    , RIGHT(d.NumberFromString, PATINDEX('%[^0-9]%', REVERSE(d.NumberFromString)) - 1 ) AS 'Right Numbers Extraction'

    /* The below checks inside the Inner string to determine if numbers exists within it.  Could be used for further processing if further extraction is necessary */
    , PATINDEX('%[0-9]%',
        SUBSTRING(d.NumberFromString /*, Start Pos, Length to Extract) */
            , PATINDEX('%[^0-9]%', d.NumberFromString)  /* Start Pos is first left non number */
            /* The below obtains the length of the Inner String so it can be extracted */
            , LEN(d.NumberFromString) - ((PATINDEX('%[^0-9]%', d.NumberFromString) -1 )) -  (PATINDEX('%[^0-9]%', REVERSE(d.NumberFromString)) -1) /* (String Length) - (LEFT Numbers) - (RIGHT Numbers) */
      )) AS innerNumExists

    /* The two lines below tell us if there exists a number at the Beginning and/or End of the string */
    , PATINDEX('%[0-9]%',  LEFT(d.NumberFromString, 1)) AS leftNumExists
    , PATINDEX('%[0-9]%', RIGHT(d.NumberFromString, 1)) AS rightNumExists

    /* Locates and returns the index of the very first number located in the string from Left to Right */
    , PATINDEX('%[^0-9]%', d.NumberFromString) AS firstLeftNonNum_index

    /* Locates and returns the index of the very first number located in the string from Right to Left */
    , LEN(d.NumberFromString) - (PATINDEX('%[^0-9]%', REVERSE(d.NumberFromString)) -1 ) AS firstRightNonNum_index

    /* Get the length of the numbers existing from Right to Left up to the first non numeric character */   
    , PATINDEX('%[^0-9]%', REVERSE(d.NumberFromString)) -1 AS rightStringLen


FROM stringNumberData d
ORDER BY 
    /* Ordering first by numbers found on the LEFT of the string */
    CAST(LEFT(d.NumberFromString, PATINDEX('%[^0-9]%', d.NumberFromString) -1) AS INT )
    /* Ordering second by numbers found on the RIGHT of the string */
    , CAST(RIGHT(d.NumberFromString, PATINDEX('%[^0-9]%', REVERSE(d.NumberFromString)) - 1 ) AS INT )
;

Answer 3

(based on answers from @shenhengbin and @EchO to this question)

The following is what I call a "clean hack". Assuming you are ordering on column Col1:

ORDER BY LEN(Col1), Col1

It is a hack, although I'd personally feel proud using it.

Answer 4

In order by statement, prepend enough zeros with when value contains any number in it to make all alphanumerica value same length

SELECT ColName
FROM TableName
ORDER BY 
 CASE WHEN ColName like '%[0-9]%' 
 THEN Replicate('0', 100 - Len(ColName)) + ColName
 ELSE ColName  END

Answer 5

You can do it using PATINDEX() function like below :

select * from Test 
order by CAST(SUBSTRING(Name + '0', PATINDEX('%[0-9]%', Name + '0'), LEN(Name + '0')) AS INT)

SQL Fiddle Demo

If you have numbers in middle of the string then you need to create small user defined function to get number from string and sort data based on that number like below :

CREATE FUNCTION dbo.fnGetNumberFromString (@strInput VARCHAR(255)) 
RETURNS VARCHAR(255) 
AS 
BEGIN 
    DECLARE @intNumber int 
    SET @intNumber = PATINDEX('%[^0-9]%', @strInput)

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

    RETURN ISNULL(@strInput,0) 
END 
GO

You can sort data by :

select Name from Test order by dbo.fnGetNumberFromString(Name), Name

Answer 6

You could adapt the function RemoveNonAlphaCharacters in this answer to filter out everything except numbers, and then use an ORDER BY using that function.

Answer 7

You could remove the first three characters and cast the rest to int

SELECT Value,
       Num=CAST(RIGHT(Value, LEN(Value) - 3) AS int)
FROM dbo.TableName
ORDER BY Num

Demo