how to sort the data in alphanumberic values

Question

i have d1,d45,d79,33,d100

i want to sort these variables in ascending order from my table so any can help me out what is the query i have to go for this on order to get the output as d1 d33 d45 d79 d100

Answer 1

Sorry, not SQL answer at all. :) For variant with one letter only order by length and alpha.

Answer 2

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

Answer 3

If you can guarantee a pattern of /\w\d+/ ...

In postgres:

select foo from bar order by cast(substring(foo from 2) as int)

..and similar will exist for other SQL flavours. Expensive mind.

edit: androids solution looks good too:

..order by char_length(foo),foo

Answer 4

If we can assume that the data values only contain the letter d and a numeric value, then you can also use:

select column from YourTable
order by convert(int, replace(column, 'd', ''))

If it contains any other letters, then this method rapidly becomes unusable:

select column from YourTable
order by convert(int, 
     replace(replace(replace(replace(replace(
      column, 'a', ''),
       'b', ''),
       'c', ''),
       'd', ''), 
       'e', '')
     )