Moving text in a string within a row

[Craig Keightley]

I have list of titles in a database, many of which begin with "the..."

Is there an sql statement to move the "the" from the beginning of the column
and place it at the end

eg "The Big Blue House" would become "Big Blue House, The"

"The Cat and Dog" would become "Cat and Dog, The"

Many Thanks

Craig

[Bill Karwin]

Re: Moving text in a string within a row

Craig Keightley wrote:[color=blue]
> eg "The Big Blue House" would become "Big Blue House, The"
> "The Cat and Dog" would become "Cat and Dog, The"[/color]

SELECT IF(POSITION("th e" IN title) > 0,
CONCAT(SUBSTRIN G(title FROM 5), ", The"),
title) AS title_alphabeti zable
FROM . . .

I tried this with a quick table in my test database, and it seems to
work. POSITION() is even case-insensitive.

Regards,
Bill K.

[Bill Karwin]

Re: Moving text in a string within a row

Bill Karwin wrote:[color=blue]
> SELECT IF(POSITION("th e" IN title) > 0,[/color]

Oops! Perhaps that string should be "the " (with a space within the
quotes), so you don't get results like "lma and Louise, The".

Regards,
Bill K.

[Craig Keightley]

Re: Moving text in a string within a row

thanks i'll give it a go
craig

"Bill Karwin" <bill@karwin.co m> wrote in message
news:cebpne0gqe @enews1.newsguy .com...[color=blue]
> Bill Karwin wrote:[color=green]
> > SELECT IF(POSITION("th e" IN title) > 0,[/color]
>
> Oops! Perhaps that string should be "the " (with a space within the
> quotes), so you don't get results like "lma and Louise, The".
>
> Regards,
> Bill K.[/color]

[Craig Keightley]

Re: Moving text in a string within a row

That works as a select statement but how do i update the records, it only
displays the results


"Craig Keightley" <dont@spam.me > wrote in message
news:410a1c6b$0 $6443$cc9e4d1f@ news-text.dial.pipex .com...[color=blue]
> thanks i'll give it a go
> craig
>
> "Bill Karwin" <bill@karwin.co m> wrote in message
> news:cebpne0gqe @enews1.newsguy .com...[color=green]
> > Bill Karwin wrote:[color=darkred]
> > > SELECT IF(POSITION("th e" IN title) > 0,[/color]
> >
> > Oops! Perhaps that string should be "the " (with a space within the
> > quotes), so you don't get results like "lma and Louise, The".
> >
> > Regards,
> > Bill K.[/color]
>
>[/color]

[Craig Keightley]

Re: Moving text in a string within a row

Fixed it
update tablename set fieldname = concat(substr(. ..), ', the') where
fieldname like 'the %'

"Craig Keightley" <dont@spam.me > wrote in message
news:410a432a$0 $6450$cc9e4d1f@ news-text.dial.pipex .com...[color=blue]
> That works as a select statement but how do i update the records, it only
> displays the results
>
>
> "Craig Keightley" <dont@spam.me > wrote in message
> news:410a1c6b$0 $6443$cc9e4d1f@ news-text.dial.pipex .com...[color=green]
> > thanks i'll give it a go
> > craig
> >
> > "Bill Karwin" <bill@karwin.co m> wrote in message
> > news:cebpne0gqe @enews1.newsguy .com...[color=darkred]
> > > Bill Karwin wrote:
> > > > SELECT IF(POSITION("th e" IN title) > 0,
> > >
> > > Oops! Perhaps that string should be "the " (with a space within the
> > > quotes), so you don't get results like "lma and Louise, The".
> > >
> > > Regards,
> > > Bill K.[/color]
> >
> >[/color]
>
>[/color]