PHP ORM Mysql ordering using REGEX

Question

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 ORM and only started last month. Please go easy on me.

Thank you very much.

Answer 1

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

make query like this in form and order by clause, according to architecture/function u r using.

One more way :

below finds ' The ','The ' at any place in string and replaces with ''.

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 ur column and table name.