If you have a column that contains some alpha and some numeric data, SQL sees the entire column as alpha and sorts the data \
differently. Here is an example of an alpha column order by:

105k
10k
120k
20k
Another Field
Yet Another Field

This is obvious since 5 (in 105k) is less than k (in 10k). This is the logical, correct way to sort alpha-numeric data. \
But I want the order to be 10k, 20k, 105k...

In SQL, the column type is defined by the table you are pulling the data from, so if the table column is int, the \
results column \
will be seen and sorted as INT by SQL.

My problem is that the column is not from a database, it is a custom column (ie: \
SELECT dd as CustomColumn FROM...), and I cannot define the type, SQL automatically does that by what you put in it, if there \
are only numbers, then SQL sees the entire column as numbers.

In order to get the above data to be natural sorted, I must add another column which only contains ints (since int columns \
will sort like 10, 20, 120...), and sort off that.

I first wrote a user defined SQL function that takes a string and removes all alpha characters, leaving only numeric (ex: \
105k -> 105, abd423de3as9->42339.

CREATE FUNCTION dbo.RemoveAlphaChars (@alphaNumString nvarchar(255))  
RETURNS nvarchar(255)
AS
BEGIN
--start function

DECLARE @num as nvarchar(255), @i as int

SET @i = 1
SET @num = ''
WHILE @i <= len(@alphaNumString) BEGIN
    IF ASCII(SUBSTRING(@alphaNumString, @i, 1)) BETWEEN 48 AND 57
        SET @num = @num + CONVERT(nvarchar(1), SUBSTRING(@alphaNumString, @i, 1))
    SET @i = @i + 1
END

RETURN @num

--end function
END

I then determined that if the first character of the data was an INT, then I should pass that data to the RemoveAlphaChars \
function above, and store that returned INT into another SQL column. If the first character is not in INT, I should store \
999999999 in the other column. If I then sort by the num field, then the name field, I get what I want.

name                 num
-------------------------
10k                  10
20k                  20
105k                 105
120k                 120
Another Field        999999999
Yet Another Field    999999999

Perfect.

My calling query is as follows:

SELECT
    tblExecutiveAnalysisOPGroups.OPCGRP_OPCGN_ID,
    tblOpCodeGroupNames.OPCGN_Name,
    CASE WHEN isnumeric(LEFT(tblOpCodeGroupNames.OPCGN_Name,1)) = 1
        THEN dbo.RemoveAlphaChars(tblOpCodeGroupNames.OPCGN_Name)
        ELSE 999999999 END as OPCGN_Name_Natural,
    COUNT(ROH_RO_NUM) as NumSrvs
FROM ...
WHERE ...
GROUP BY ...
ORDER BY tblOpCodeGroupNames.OPCGN_Name_Natural, tblOpCodeGroupNames.OPCGN_Name ASC

Notice the CASE and the ORDER BY which does all the magic.

The query above, is the real query I was working on, so does not match the test data above, the real output looks \
like this

OPCGRP_OPCGN_ID                         OPCGN_Name    OPCGN_Name_Natural   NumSrvs
----------------------------------------------------------------------------------
44F20FD4-B8C7-433E-A8A1-004BA713DA83    15K           15                   48
580E681F-B157-47D9-BFEA-32056CF1ED6D    120K          120                  7
DA3853B4-691C-45DB-B1D2-5F18A5346DDE    Coolant Flush 999999999            17
E9F58279-0085-4AAD-A096-67989F8FA5C2    LOF           999999999            350
C769D9C2-25D5-4A7C-AA9D-CBB79D24136A    Major Menu    999999999            25
442EEE27-1A5F-4C48-89E4-A258ABD09050    Other Menu    999999999            13

Keywords

MSSQL SQL natural sort order by numeric