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]

Craig Keightley wrote:

eg "The Big Blue House" would become "Big Blue House, The"
"The Cat and Dog" would become "Cat and Dog, The"

SELECT IF(POSITION("the" IN title) > 0,
CONCAT(SUBSTRING(title FROM 5), ", The"),
title) AS title_alphabetizable
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]

Bill Karwin wrote:

SELECT IF(POSITION("the" 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.

[Craig Keightley]

thanks i'll give it a go
craig

"Bill Karwin" <bi**@karwin.com> wrote in message
news:ce********@enews1.newsguy.com...

Bill Karwin wrote:

SELECT IF(POSITION("the" 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.

[Craig Keightley]

That works as a select statement but how do i update the records, it only
displays the results
"Craig Keightley" <do**@spam.me> wrote in message
news:41**********************@news-text.dial.pipex.com...

thanks i'll give it a go
craig

"Bill Karwin" <bi**@karwin.com> wrote in message
news:ce********@enews1.newsguy.com...

Bill Karwin wrote:

SELECT IF(POSITION("the" 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.

[Craig Keightley]

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

"Craig Keightley" <do**@spam.me> wrote in message
news:41**********************@news-text.dial.pipex.com...

That works as a select statement but how do i update the records, it only
displays the results
"Craig Keightley" <do**@spam.me> wrote in message
news:41**********************@news-text.dial.pipex.com...

thanks i'll give it a go
craig

"Bill Karwin" <bi**@karwin.com> wrote in message
news:ce********@enews1.newsguy.com...

Bill Karwin wrote:
> SELECT IF(POSITION("the" 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.