ORDER / SORT Mixed number and column number with char prefix

Question

I have a varchar column encoding code, this code can only contain numbers or a number prefixed with char, for example, I have a column containing this data:

+------+ | Code | +------+ | 1 | | C1 | | 2 | | 3 | | C3 | | F3 | | F1 | | F17 | | C9 | | C10 | | C47 | | C100 | | C134 | | A234 | |C1245 | | 10 | | 100 | +------+ 

Etc...

I want to sort this column according to the following rules:

• Numeric Code Only
• The prefix code with alphabetic ordering is alfanumerically and the numerical part ordered as a number

I want to get a result set ordered as follows:

 +------+ | Code | +------+ | 1 | | 2 | | 3 | | 10 | | 100 | | A234 | | C1 | | C3 | | C9 | | C10 | | C47 | | C100 | | C134 | |C1245 | | F1 | | F3 | | F17 | +------+ 

How can I get a result set ordered by these criteria? I tried with this request:

 SELECT Code FROM Code_List ORDER BY case when Code like '%[az]%' then 99999999999999999999999999999999 else convert(decimal, Code) end 

But I get a result that first orders the number, and then the prefix number, but the alpha prefix number is ordered as char, and not the way I want it ...

A single numerical entry must be ordered in accordance with the rules of numerical order and character order, therefore, if a single numerical entry:

 +------+ | Code | +------+ | 1 | | 47 | | 2 | | 3 | | 6 | | 100 | | 112 | | 10 | 

I want to receive:

 +------+ | Code | +------+ | 1 | | 2 | | 3 | | 6 | | 10 | | 47 | | 100 | | 112 | 

Database is Microsoft SQL Server.

Answer 1

Assuming there are no spaces before the values, and there can only be a 1-char prefix:

 ORDER BY CASE WHEN LEFT(Code, 1) BETWEEN '0' AND '9' THEN ' ' ELSE LEFT(Code, 1) END, CAST(STUFF(Code, 1, CASE WHEN LEFT(Code, 1) BETWEEN '0' AND '9' THEN 0 ELSE 1 END, '') AS int) 

Alternatively, the second criterion can be rewritten as follows:

  CAST(STUFF(Code, 1, PATINDEX('[^0-9]%', Code), '') AS int) 

PATINDEX('[^0-9]%', Code) returns 1 if it finds a non-numeric character at the beginning of Code and 0 otherwise. As a result, STUFF either deletes 1 character or not, i.e. The same as before.

Answer 2

  CASE WHEN ISNUMERIC(Col) = 1 THEN '@' Else LEFT(Col, 1) END ,CASE WHEN ISNUMERIC(Col) = 1 THEN Convert(int, Col) Else Convert(int, RIGHT(Col, LEN(Col) - 1)) END 

Answer 3

you may try

 ...ORDER by ('A'+Code) 

Answer 4

 SELECT Code FROM Code_List ORDER BY Code, CASE WHEN ISNUMERIC(SUBSTRING(Code,1,1)) = 1 THEN CODE ELSE SUBSTRING(Code,2,LEN(Code)-1) END 

Obviously, assuming that only the first digit can be alpha