PDA

View Full Version : Ignoring in (ORDER BY)…

abdul_a
02-06-02, 14:31
Hi,

Is there a way to ignore some alphabet when ordering names by ASC or DESC

A sample :

I have a table (first_name) :

- The test.
- Falcon
- The Armageddon
- Battle


When ordering it will be :

Battle
Falcon
The Armageddon
The test.

…

is there a command that ignore (The) in the order, so it will be :

The Armageddon
Battle
Falcon
The test.


?
Paul
02-06-02, 20:15
Use the SUBSTRING function in the order by clause. ie.

SELECT title, description
FROM mytable
ORDER BY SUBSTRING(title,5);

This will only use letters starting at position 5.

MySQL has quite a good selection of string functions. There are probably other ways to this. See here (http://www.mysql.com/doc/S/t/String_functions.html)
abdul_a
02-08-02, 23:26
thanks

it works with english alphabet,

but it dosn't work with arabic alphabet :confused:
achorozy
02-11-02, 12:56
Actually the solution that Paul gave works for that example, since the 5th character of each word does place the word in order:

l - Battle
o - Falcon
a - The Armageddon
t - The test

Reordered

a - The Armageddon
l - Battle
o - Falcon
t - The test

This code will work without having to figure out which position should you ORDER BY.

select * from tbl order by REPLACE(col1,"The ","");