PHP ORM Mysql ordering using REGEX

You see I have a set of entries to be ordered alphabetically. Though some of the entries starts with "The". What I want is to ignore "The" and start sorting from the next word. For example:

$titles->order_by("name", "ASC")->find_all() // Sample query

• Abraham
• Panorama
• The Malevolent

What I want:

• Abraham
• The Malevolent // Ignore "the" in the sorting
• Panorama

What I really really want"

• Abraham
• Malevolent, The // Kinda rearranged
• Panorama

How can I do that here?:

$titles->order_by("name", "ASC")->find_all();

If not then what may you suggest?

I have a strong hunch that using REGEX would seal the deal. Though I don't know how without going into the software level.

Im using Kohana 3开发者_如何学C ORM and only started last month. Please go easy on me.

Thank you very much.

You may use a conditional select and a substring function; the exact name of these functions may vary depending on the dialect you use. In Transact-SQL:

select if( left(name,3)='The', SUBSTRING(name,5), name) as name 
from table 
order by name

And another way: use a conditional to find the 'The ' part, and replace it with an empty string:

select TRIM(
  if(INSTR(shipnam,'The ') > 0, INSERT(shipnam,INSTR(shipnam,'Hyundai'),4,''),
  if(INSTR(shipnam,' The ') > 0, INSERT(shipnam,INSTR(shipnam,'Hyundai'),5,''),shipnam))
) 
as shipname from tablename order by shipname

Change according to your column and table name.