105k
10k
120k
20k
Another Field
Yet Another Field
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
name num
-------------------------
10k 10
20k 20
105k 105
120k 120
Another Field 999999999
Yet Another Field 999999999
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
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
MSSQL SQL natural sort order by numeric