locked
how to do Ordering as alphabets first and Numeric ? RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hello All,

    ORDER BY orders the value as
    1) Special characters
    2) Numeric
    3) Characters


    I want to ORDER BY the values as

    1) Characters
    2) Numeric
    3) Special Characters

    for Eg.

    1Yamaha, Suzuki, **Ducati, Harley Davidson, 2Honda

    should be in the order

    Harley Davidson
    Suzuki
    1Yamaha
    2Honda
    **Ducati 

    Thanks.
    Tuesday, September 14, 2010 1:05 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    What if you have number in the middle?

    Assuming you only concerned for the numbers at the beginning of the string, then

    declare @t table ([Car Name] varchar(100))
insert into @t 
values ('1Yamaha'), ('Suzuki'), ('**Ducati'),('Harley Davidson'), ('2Honda')

select * from @t 
order by case when [Car Name] like '[0-9]%' then 100 when [Car Name] like '[^a-z]%' then 200 else 0 end, [Car Name]

    This will sort alphas before numericals 

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Ramaraj Kumar Tuesday, September 14, 2010 1:28 PM
    Tuesday, September 14, 2010 1:12 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    What if you have number in the middle?

    Assuming you only concerned for the numbers at the beginning of the string, then

    declare @t table ([Car Name] varchar(100))
insert into @t 
values ('1Yamaha'), ('Suzuki'), ('**Ducati'),('Harley Davidson'), ('2Honda')

select * from @t 
order by case when [Car Name] like '[0-9]%' then 100 when [Car Name] like '[^a-z]%' then 200 else 0 end, [Car Name]

    This will sort alphas before numericals 

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Ramaraj Kumar Tuesday, September 14, 2010 1:28 PM
    Tuesday, September 14, 2010 1:12 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    No need to consider the numbers in the middle.

    The query you gave ordering values as i wish.

     

    Thank you Naom.

    Tuesday, September 14, 2010 1:30 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Naomi,

     

    Why did you use 200, 100 in CASE statement. Can you explain little more about that please?

     

    Thanks,

    Tuesday, September 14, 2010 1:32 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Just picked up the numbers out of the blue. I could have used 0,1,2 or anything else. I just wanted to ensure that numbers will be second in the sequence, letters first and all not numbers/not alphas last. So, I could have used 0 for letters, 1  for numbers, 2 for everything else.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, September 14, 2010 1:36 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    In the query

    if the value starts with 0 to  9 then it is declared as 100

    if the value not starts with a to z ie special characters then declared as 200

    others( which are characters fro m a to z ) declared as 0

     

    So the query automatically order the values by

     

    0 --> the alphabets

    100 --> the numbers

    200 --> the special characters.

    Tuesday, September 14, 2010 1:38 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Just picked up the numbers out of the blue. I could have used 0,1,2 or anything else. I just wanted to ensure that numbers will be second in the sequence, letters first and all not numbers/not alphas last. So, I could have used 0 for letters, 1  for numbers, 2 for everything else.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog


    Nice..

     

    Got it.. I should have got the point. But its morning without Coffee.. Thanks for your nice explanation.

    Tuesday, September 14, 2010 1:38 PM