Right,
i have all my attention on the "GGGG The Yeti" order, and didn't see the rest.
This is the right structure including "The" in the middle :
mysql> SELECT * FROM names ORDER BY case when substring(name,1,3)='The' then
REPLACE(name,'The ','')
-> else name end;
+----------------+
| name |
+----------------+
| AAAA |
| AAAA |
| The AAAA |
| The AAAA |
| BBBB |
| GGGG |
| GGGG The Yeti |
| GGGG Xylophone |
| GGGG Zyxel |
| woohoo |
| The ZZZZ |
| The ZZZZ |
| ZZZZ |
+----------------+
13 rows in set (0.02 sec)
Hope that's better
Mathias
Selon Hassan Schroeder <[EMAIL PROTECTED]>:
> Mathias wrote:
>
> > you didn't give an alternative, but i've forgotten just a '^' :
>
> > mysql> SELECT * FROM names ORDER BY REPLACE(name,'^The ','');
>
> No, sorry -- that doesn't work at all; REPLACE takes a string,
> not a regex. Look at your example below: 'The AAAA' should be
> after 'AAAA'; 'ZZZZ' should be before 'The ZZZZ'. And so on.
> > +----------------+
> > | name |
> > +----------------+
> > | AAAA |
> > | AAAA |
> > | BBBB |
> > | GGGG |
> > | GGGG The Yeti | <==== Rigth order
> > | GGGG Xylophone |
> > | GGGG Zyxel |
> > | The AAAA |
> > | The AAAA |
> > | The ZZZZ |
> > | The ZZZZ |
> > | ZZZZ |
> > +----------------+
>
> Quick test: SELECT REPLACE(name,'^The ','woohoo') FROM names; -- :-)
>
> --
> Hassan Schroeder ----------------------------- [EMAIL PROTECTED]
> Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com
>
> dream. code.
>
>
>
> --
> 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]
