Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I sort a VARCHAR column in SQL server that contains numbers?

Tags:

sql

tsql

I have a VARCHAR column in a SQL Server 2000 database that can contain either letters or numbers. It depends on how the application is configured on the front-end for the customer.

When it does contain numbers, I want it to be sorted numerically, e.g. as "1", "2", "10" instead of "1", "10", "2". Fields containing just letters, or letters and numbers (such as 'A1') can be sorted alphabetically as normal. For example, this would be an acceptable sort order.

1 2 10 A B B1 

What is the best way to achieve this?

like image 947
Tim C Avatar asked Sep 23 '08 08:09

Tim C


People also ask

How do I sort varchar numbers?

'LPAD(lower(column_name))' is used to sort the varchar field numerically in MySQL.

How do I sort a column by number in SQL?

The ORDER BY statement in SQL is used to sort the fetched data in either ascending or descending according to one or more columns. By default ORDER BY sorts the data in ascending order. We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.

How do I convert varchar to numeric in SQL?

To convert a varchar type to a numeric type, change the target type as numeric or BIGNUMERIC as shown in the example below: SELECT CAST('344' AS NUMERIC) AS NUMERIC; SELECT CAST('344' AS BIGNUMERIC) AS big_numeric; The queries above should return the specified value converted to numeric and big numeric.


1 Answers

One possible solution is to pad the numeric values with a character in front so that all are of the same string length.

Here is an example using that approach:

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

The 100 should be replaced with the actual length of that column.

like image 99
Aleris Avatar answered Sep 27 '22 21:09

Aleris