How to sort a VARCHAR column on a SQL server that contains numbers?

Question

I have a VARCHAR column in a SQL Server 2000 that can contain either letters or numbers. It depends on how the application is configured on the interface for the client.

When it contains numbers, I want it to be sorted numerically, for example. like "1", "2", "10" instead of "1", "10", "2". Fields containing only letters, letters or numbers (for example, “A1”) can be sorted alphabetically as usual. For example, this would be an acceptable sort order.

 1 2 10 A B B1 

What is the best way to achieve this?

Answer 1

One possible solution is to overlay numerical values ​​with the character in front, so that everyone has the same string length.

Here is an example of using this approach:

 select MyColumn from MyTable order by case IsNumeric(MyColumn) when 1 then Replicate('0', 100 - Len(MyColumn)) + MyColumn else MyColumn end 

100 should be replaced with the actual length of this column.

Answer 2

There are several possible ways to do this.

Could

 SELECT ... ORDER BY CASE WHEN ISNUMERIC(value) = 1 THEN CONVERT(INT, value) ELSE 9999999 -- or something huge END, value 

the first part of ORDER BY converts everything to int (with a huge value for non-numeric numbers, for sorting the latter), then the last part takes care of the alphabets.

Note that the performance of this request is probably at least moderately terrible on large amounts of data.

Answer 3

 select Field1, Field2... from Table1 order by isnumeric(Field1) desc, case when isnumeric(Field1) = 1 then cast(Field1 as int) else null end, Field1 

This will return the values ​​in the order you indicated in your question.

Performance will not be too great when doing all these castings, so another approach is to add another column to the table in which you store an integer copy of the data, and then sort first and then the corresponding column, Obviously, this will require some changes in logic that inserts or updates data in a table to populate both columns. Either this, or put a trigger in the table to populate the second column whenever data is inserted or updated.

Answer 4

 SELECT *, CONVERT(int, your_column) AS your_column_int FROM your_table ORDER BY your_column_int 

OR

 SELECT *, CAST(your_column AS int) AS your_column_int FROM your_table ORDER BY your_column_int 

Both are pretty portable, I think.

Answer 5

I solved it very simply by writing it in the "order" part

 ORDER BY ( sr.codice +0 ) ASC 

This seems to work very well, in fact I had the following sort:

 16079 Customer X 016082 Customer Y 16413 Customer Z 

So, 0 before 16082 is considered correct.

Answer 6

you can always convert your varchar-column to bigint as an integer might be too short ...

 select cast([yourvarchar] as BIGINT) 

but you should always take care of alpha characters

 where ISNUMERIC([yourvarchar] +'e0') = 1 

the + 'e0' comes from http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/isnumeric-isint-isnumber

this will lead to your statement

 SELECT * FROM Table ORDER BY ISNUMERIC([yourvarchar] +'e0') DESC , LEN([yourvarchar]) ASC 

the first sort column will place the numeric value on top. the second sorts by length, so 10 will be preceded by 0001 (what's stupid ?!)

this leads to the second version:

 SELECT * FROM Table ORDER BY ISNUMERIC([yourvarchar] +'e0') DESC , RIGHT('00000000000000000000'+[yourvarchar], 20) ASC 

the second column now gets the right to "0", so natural sorting puts integers with leading zeros (0,01,10,0100 ...) in the correct order (right!) - but all alpha will be expanded using '0'- characters (execution)

therefore the third version:

  SELECT * FROM Table ORDER BY ISNUMERIC([yourvarchar] +'e0') DESC , CASE WHEN ISNUMERIC([yourvarchar] +'e0') = 1 THEN RIGHT('00000000000000000000' + [yourvarchar], 20) ASC ELSE LTRIM(RTRIM([yourvarchar])) END ASC 

now the numbers are first filled with "0'-characters" (of course, the length of 20 can be increased) - which sorts the numbers on the right - and alpha only cuts off

Answer 7

It works:

 select your_column from your_table order by case when isnumeric(your_column) = 1 then your_column else 999999999 end, your_column 

Answer 8

 SELECT FIELD FROM TABLE ORDER BY isnumeric(FIELD) desc, CASE ISNUMERIC(test) WHEN 1 THEN CAST(CAST(test AS MONEY) AS INT) ELSE NULL END, FIELD 

According to this link, you need to pass in MONEY, then INT, to avoid the order of $$ as a number.

Answer 9

This request is useful to you. In this query, the column is of the varchar data type, ordered in good order. For example: this column shows the data: - G1, G34, G10, G3. So, after running this query, you will see the results: - G1, G10, G3, G34.

 SELECT *, (CASE WHEN ISNUMERIC(column_name) = 1 THEN 0 ELSE 1 END) IsNum FROM table_name ORDER BY IsNum, LEN(column_name), column_name; 

Answer 10

It may help you, I tried it when I had the same problem.

SELECT * FROM tab ORDER BY IIF(TRY_CAST(val AS INT) IS NULL, 1, 0),TRY_CAST(val AS INT);

Answer 11

This is easier than the above methods:

  SELECT * FROM table_name ORDER BY LENGTH(column_name), column_name 

Answer 12

  SELECT *, ROW_NUMBER()OVER(ORDER BY CASE WHEN ISNUMERIC (ID)=1 THEN CONVERT(NUMERIC(20,2),SUBSTRING(Id, PATINDEX('%[0-9]%', Id), LEN(Id)))END DESC)Rn ---- numerical FROM ( SELECT '1'Id UNION ALL SELECT '25.20' Id UNION ALL SELECT 'A115' Id UNION ALL SELECT '2541' Id UNION ALL SELECT '571.50' Id UNION ALL SELECT '67' Id UNION ALL SELECT 'B48' Id UNION ALL SELECT '500' Id UNION ALL SELECT '147.54' Id UNION ALL SELECT 'A-100' Id )A ORDER BY CASE WHEN ISNUMERIC (ID)=0 /* alphabetical sort */ THEN CASE WHEN PATINDEX('%[0-9]%', Id)=0 THEN LEFT(Id,PATINDEX('%[0-9]%',Id)) ELSE LEFT(Id,PATINDEX('%[0-9]%',Id)-1) END END DESC