[SOLVED] SQL ORDER BY excluding 'The '

[karldesign]

Hi

 

I have a query that I would like to perform, but am struggling with producing it.

 

Say you have a list of titles (ie: The Spanish, English, The French, Belgians) and I want to ORDER BY title ASC, how do I order them, but ignore the 'The ' in the SQL query.

 

So the output order would be: Belgians, English, The French, The Spanish?

 

Thanks in advance for any help!

[Gibbs]

I don't fully understand them in MySQL but if you don't get any useful replies (which is doubtful  ) look up using SUBSTRING.

 

Do they ALL have "The"? If so it would probably be easier...

[clown[NOR]]

maybe... just maybe... you make a field in the table named "FullTitle" and one name "ShortTitle" and in LongTitle you have "The" included but on short you have it removed? so then you order by ShortTitle but the output will be the LongTitle..

 

just an idea... i'm new to mysql, looking forward to see the answers from someone more experienced =)

[karldesign]

maybe... just maybe... you make a field in the table named "FullTitle" and one name "ShortTitle"

 

This could be good... I'm just thinking its a bit long winded for something that, effectively, should be simple... I think?!

[Gibbs]

If they're ALL The (for example The English, The French etc) then the below *should* work. You will need to change 15 to the longest string you have.

 

For example The English (if that's your longest word) would be 5, 7. The first number is where you want the string starts, the second is the length to be extracted.

 

SELECT SUBSTRING(`yourfield`, 5, 7) + 0 AS nationality, tb.* FROM `yourtable` tb ORDER BY nationality ASC

 

Something like

 

$query = mysql_query("SELECT SUBSTRING(`yourfield`, 7, 15) + 0 AS nationality, tb.* FROM `yourtable` tb ORDER BY nationality DESC");
while ($result = mysql_fetch_array($query))
{
echo "$result[yourfield]<br />";
}

[karldesign]

Some may not have 'The '...

 

I have found this:

 

SELECT *, IF ( LEFT( vch_title, 4)='The ', SUBSTRING(vch_title FROM 4), vch_title ) AS title_sort FROM tbl_links WHERE int_category = '$i' ORDER BY title_sort;

 

The problem is, all the ones that start with 'The ' accumulate at the top before alphabetically ordering the reamaining fields...

[Gibbs]

Some may not have 'The '...

 

I have found this:

 

SELECT *, IF ( LEFT( vch_title, 4)='The ', SUBSTRING(vch_title FROM 4), vch_title ) AS title_sort FROM tbl_links WHERE int_category = '$i' ORDER BY title_sort;

 

The problem is, all the ones that start with 'The ' accumulate at the top before alphabetically ordering the reamaining fields...

 

Try add ASC after title_sort

[karldesign]

Still no joy - the 'The ' still come first...

[Tyche]

The following code will ignore the string "The " in the orderering of records

 

SELECT your_title_field FROM your_table ORDER BY REPLACE(your_title_field,"The ","");

 

It will ignore "The " wherever it appears not just at the front of the title string so may have a few undesired effects but will certainly sort your example as you want it

[fenway]

Some may not have 'The '...

 

I have found this:

 

SELECT *, IF ( LEFT( vch_title, 4)='The ', SUBSTRING(vch_title FROM 4), vch_title ) AS title_sort FROM tbl_links WHERE int_category = '$i' ORDER BY title_sort;

 

The problem is, all the ones that start with 'The ' accumulate at the top before alphabetically ordering the reamaining fields...

Not sure why this wouldn't work...

[Tyche]

That does work if the "FROM 4" is changed to ",5" (and then is better than my suggestion  )

As below :

SELECT *, IF ( LEFT( vch_title, 4)='The ', SUBSTRING(vch_title,5), vch_title ) AS title_sort FROM tbl_links WHERE int_category = '$i' ORDER BY title_sort;