Sorting VARCHAR column which contains integers

Question

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

What about `'C1'`, how does that work? Would `'C2'` be **greater** than `'c10'`? — Thom A, Oct 22 '21 at 19:35
Yes, C2 is Greater than C10, but that part doesn't matter much. Either way works. Most important are beginning integers — Eric Klaus, Oct 22 '21 at 19:38
@AaronBertrand, that part doesn't matter. Ideally C4C comes before C41C. Doesn't matter though — Eric Klaus, Oct 22 '21 at 19:39
So are values that have a numerical value the the start always sorted first, then those values by the numerical value, and then everything else alphabetically? — Thom A, Oct 22 '21 at 19:41
Or do you mean you don't care if it's mixed alphanumeric, sort by only numbers then only letters? — Stu, Oct 22 '21 at 19:41
If you use this ORDER BY clause, you will get what you want, but first the letters and then the numbers: order by cast(left(Col, patindex('%[^0-9]%', Col+'.') - 1) as int). Would it be valid? — Carlos, Oct 22 '21 at 19:56
That would order the values that don't have a integer prefix first, @Carlos . — Thom A, Oct 22 '21 at 19:58

score 2 · Accepted Answer · answered Oct 22 '21 at 19:53

2

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;

answered Oct 22 '21 at 19:53

Thom A

88,727
11
45
75

Sorry, got distracted! – Aaron Bertrand Oct 22 '21 at 20:40
I'm confused, @AaronBertrand, by what? – Thom A Oct 22 '21 at 20:41
(1) I started working on a fiddle (2) got distracted (3) posted my answer (4) noticed you posted an answer in the meantime (5) got distracted again before I gave you the upvote you deserve. :-) I'll probably for the rest of my career gravitate toward CTEs over CROSS APPLY for avoiding repetition. – Aaron Bertrand Oct 22 '21 at 20:42
Don't worry, I got distracted bhy the F1 at least twice while writing the above, @AaronBertrand . :) – Thom A Oct 22 '21 at 20:45
@AaronBertrand `APPLY` is absolutely the bee's knees, I would be absolutely crippled in SQL without it. See https://stackoverflow.com/a/65818648/14868997 It's so much easier than repetitive CTEs or nested derived tables – Charlieface Oct 23 '21 at 20:54

score 2 · Answer 2 · answered Oct 22 '21 at 20:25

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:

Col
1
1D
2
10
10HH
A
B
C1

Example db<>fiddle

Sorting VARCHAR column which contains integers

2 Answers2