How to sort data by alphanumeric values
I have the following values: d1, d45, d79, d33, d100
I want to sort these variables in ascending order from my table.
What is the request to get the output as:
d1
d33
d45
d79
d100
What you want is called "natural". For Microsoft SQL Server 2005 see this question . For other languages see (For example) this other question .
Sorry, not a SQL answer at all. :) For the single letter variant, only order by length and alpha.
If you can guarantee the pattern / \ w \ d + / ...
In postgres:
select foo from bar order by cast(substring(foo from 2) as int)
.. and a similar one will exist for other SQL flavors. Dear mind.
edit: androids solution looks good too:
..order by char_length(foo),foo
If we can assume that the data values only contain the letter d and a numeric value, you can also use:
select column from YourTable
order by convert(int, replace(column, 'd', ''))
If it contains any other letters, then this method quickly becomes unusable:
select column from YourTable
order by convert(int,
replace(replace(replace(replace(replace(
column, 'a', ''),
'b', ''),
'c', ''),
'd', ''),
'e', '')
)