I have this table:

IF OBJECT_ID('tempdb..#Test') IS NOT NULL 
    DROP TABLE #Test;

CREATE TABLE #Test (Col VARCHAR(100));

INSERT INTO #Test 
VALUES ('1'), ('2'), ('10'), ('A'), ('B'), ('C1'), ('1D'), ('10HH')

SELECT * FROM #Test

I want to sort by numeric value first and then alphabetically.

Outcome of sort I want to is:

1
1D
2
10
10HH
A
B
C1

Assume structure of entries is one of those (with no dash of course)

number
number-string
string-number
string

if there is an entry like string-number-string, assume it is string-number

CodePudding user response：

It's not pretty, but it works.

SELECT T.Col
FROM #Test T
     CROSS APPLY (VALUES(PATINDEX('%[^0-9]%',T.Col)))PI(I)
     CROSS APPLY (VALUES(TRY_CONVERT(int,NULLIF(ISNULL(LEFT(T.Col,NULLIF(PI.I,0)-1),LEN(T.Col)),''))))TC(L)
ORDER BY CASE WHEN TC.L IS NULL THEN 1 ELSE 0 END,
         TC.L,
         T.Col;

Honestly, I would suggest that if you want to order your data like a numerical value you actually store the numerical value in a numerical column; clearly the above should be a numerical prefix value, and then the string suffix. If you then want to then have the values you have, the use a (PERSISTED) computed column. Like this:

CREATE TABLE #Test (Prefix int NULL,
                    Suffix varchar(100) NULL,
                    Col AS CONCAT(Prefix, Suffix) PERSISTED);

INSERT INTO #Test (Prefix, Suffix)
VALUES (1,NULL), (2,NULL), (10,NULL), (NULL,'A'), (NULL,'B'), (NULL,'C1'), (1,'D'), (10,'HH');

SELECT Col
FROM #Test
ORDER BY CASE WHEN Prefix IS NULL THEN 1 ELSE 0 END,
         Prefix,
         Suffix;

CodePudding user response：

This awful and unintuitive solution, that would be unnecessary if you stored the two pieces of data separately, brought to you by bad idea designs™:

;WITH cte AS 
(
  SELECT Col, rest = SUBSTRING(Col, pos, 100),
    possible_int = TRY_CONVERT(bigint, CASE WHEN pos <> 1 THEN 
    LEFT(Col, COALESCE(NULLIF(pos,0),100)-1) END)
  FROM (SELECT Col, pos = PATINDEX('%[^0-9]%', Col) FROM #Test) AS src
)
SELECT Col FROM cte
ORDER BY CASE 
  WHEN possible_int IS NULL THEN 2 ELSE 1 END, 
  possible_int, 
  rest;

Result:

• Example db<>fiddle