Hi, you didn't give an alternative, but i've forgotten just a '^' : mysql> SELECT * FROM names ORDER BY REPLACE(name,'The ',''); +----------------+ | name | +----------------+ | AAAA | | The AAAA | | The AAAA | | AAAA | | BBBB | | GGGG | | GGGG Xylophone | | GGGG The Yeti | | GGGG Zyxel | | The ZZZZ | | ZZZZ | | The ZZZZ | +----------------+ 12 rows in set (0.00 sec)
mysql> mysql> mysql> mysql> SELECT * FROM names ORDER BY REPLACE(name,'^The ',''); +----------------+ | name | +----------------+ | AAAA | | AAAA | | BBBB | | GGGG | | GGGG The Yeti | <==== Rigth order | GGGG Xylophone | | GGGG Zyxel | | The AAAA | | The AAAA | | The ZZZZ | | The ZZZZ | | ZZZZ | +----------------+ 12 rows in set (0.00 sec) Mathias Selon Sergey Spivak <[EMAIL PROTECTED]>: > Hi > > > this,among other answers, can be done : > > > > mysql> select * from names; > > +----------+ > > | name | > > +----------+ > > | AAAA | > > | The AAAA | > > | ZZZZ | > > | The ZZZZ | > > | BBBB | > > +----------+ > > 5 rows in set (0.02 sec) > > > > mysql> select * from names order by replace(name,'The ',''); > > +----------+ > > | name | > > +----------+ > > | AAAA | > > | The AAAA | > > | BBBB | > > | ZZZZ | > > | The ZZZZ | > > +----------+ > > 5 rows in set (0.00 sec) > > > > Hmm... > Disanvantage of such way is replacing of 'The ' substring > in *ANY* place of field, not just at the beginning of it. :( > > Look here: > > mysql> SELECT * FROM names; > +----------------+ > | name | > +----------------+ > | AAAA | > | The ZZZZ | > | GGGG | > | GGGG The Yeti | > | The AAAA | > | GGGG Xylophone | > | GGGG Zyxel | > +----------------+ > 7 rows in set (0.00 sec) > > mysql> SELECT * FROM names ORDER BY REPLACE(name,'The ',''); > +----------------+ > | name | > +----------------+ > | AAAA | > | The AAAA | > | GGGG | > | GGGG Xylophone | > | GGGG The Yeti | <--- must be earlier :) > | GGGG Zyxel | > | The ZZZZ | > +----------------+ > 7 rows in set (0.00 sec) > > -- > wbr, sergey v. spivak > sergey#spivak.kiev.ua > zlob-uanic/eunic/ripe > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
