Clever Geek Handbook

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
0


source to share


4 answers


What you want is called "natural". For Microsoft SQL Server 2005 see this question . For other languages ​​see (For example) this other question .



+1


source


Sorry, not a SQL answer at all. :) For the single letter variant, only order by length and alpha.



0


source


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
0


source


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', '')
        )
0


source






More articles:


All Articles