SQL Server 2008 - order by strings with number numerically

(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.

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

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

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

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

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 )
;

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