[SOLVED] Ignore "The" while alphabetically sorting an associative array from an SQL query

[ofs]

I've got a script selecting two columns (name, url) from a database, and then using while(mysql_fetch_assoc()) to output each row.

 

I want to know how to order the output alphabetically by the NAME column, but ignore the word "The" before any of the names. For example:

Banana
The Apple
Orange
The Lemon

Will be re-arranged to:

The Apple
Banana
The Lemon
Orange

It doesn't matter to me whether this sort is done in the MySQL query, or after the fact. Any ideas?

 

Thanks.

[vbnullchar]

well i have a workaround but its working

 

SELECT name, url, SUBSTRING(name,3) as name2 FROM table 
ORDER BY SUBSTRING(name,3) ASC
[code]

[/code]

[ofs]

I appreciate the reply, but the problem with that is that not all of the names start with the word "The", only a few of them.

[miracle_potential]

$query = mysql_query("SELECT * FROM table ORDER BY name DESC") or die(mysql_error());
while($row = mysql_fetch_assoc($query){

echo str_replace('the ', ' ', $row['name']);

}

 

 

--EDIT--

Oops sorry misunderstood the question hang on

[miracle_potential]

$query = mysql_query("SELECT * FROM table NOT RLIKE '^[the ]' AS c.name ORDER BY c")or die("error");

 

while($row = mysql_fetch_assoc($query){

 

echo $row['c'];

 

}

 

Not sure if this is going to work to be totally honest because I havnt tested it and I'm not a Regex programmer but its deffo a hint as to where you should be looking. Hope ya get the idea I'm shattered!  :-X :-\

 

[ofs]

I tried that and it didn't work, but you helped me in finding a way to do it:

 

SELECT name, url FROM shows ORDER BY REPLACE(name, 'The ', '')

 

I never knew that function existed, but it works perfectly. And since it's only in the ORDER BY clause, it doesn't output the replaced name.

 

Thanks!