CODEWITHSUNDEEP
sqldatabase
Vijay
Vijay

Reputation: 1016

Sorting alphanumeric data

Hi i want to sort alphanumeric data in ascending order .

data like this : 1mac , apple , dom , 007bind , pcworld , 8basic , nothing.

But here I want the result such as : apple, dom ,nothing, pcworld , 007bind , 1mac , 8basic

can anyone tell me the sql query to show such result. Any help will be appreciated. Thanks In Advance..

Upvotes: 1

Views: 971

Answers (3)

Brandon Moore
Brandon Moore

Reputation: 8780

Here's one way to do it:

select col from sometable where left(col, 1) < '0' or left(col, 1) > '9' order by col
union all
select col from sometable where left(col, 1) >= '0' and left(col, 1) <= '9' order by col

Upvotes: 1

matino
matino

Reputation: 17725

SELECT your_field FROM your_table ORDER BY (your_field + 0 <> 0 OR your_field = '0') ASC, your_field + 0, your_field

Upvotes: 1

Udo Held
Udo Held

Reputation: 12538

You have to check if your database vendor supports such a collation and set the collation accordingly.

http://www.collation-charts.org

E.g. oracle has http://www.collation-charts.org/oracle10g/ora10g.CL8MSWIN1251.GENERIC_BASELETTER.html

Upvotes: 1

Related Questions