# how to do Ordering as alphabets first and Numeric ?

• ### Question

• 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

• 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 Tuesday, September 14, 2010 1:28 PM
Tuesday, September 14, 2010 1:12 PM

### All replies

• 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 Tuesday, September 14, 2010 1:28 PM
Tuesday, September 14, 2010 1:12 PM
• 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
• 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
• 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
• 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
• 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