MySQL, moving first word to last

How wo开发者_如何转开发uld I move the first word, if it was the word 'the', to the end of the cell in MySQL?

As an example:

before: The Good, the Bad and the Ugly after: Good, the Bad and the Ugly, The

Thanks

I agree with thecoshman that you should be careful not to do too much processing on the SQL side, but the following query may accomplish what you're looking for:

SELECT CONCAT(
        SUBSTR(col, LOCATE(' ', col)+1), 
        ' ', 
        SUBSTRING_INDEX(col, ' ', 1)
    ) FROM table;

You wouldn't use SQL for this. SQL is for querying databaes. You would read that value from the database, edit it with some code such as PHP, perl, ruby, C#, ASP etc. etc. Then write the value back into the database.

Do you mean to change the data in the existing table?

UPDATE films
SET title= CONCAT(UPPER(SUBSTR(title, 5, 1)), SUBSTR(title, 6), ', The')
WHERE title LIKE 'The %';

However I agree with thecoshman: this is usually better done in a code outside the database. SQL's string-processing abilities are generally weak and inconvenient compared to a real scripting language.

Small correction in your query, but nice result.

SELECT CONCAT(
        SUBSTRING(col, LOCATE(' ', col)+1), 
        ' ', 
        SUBSTRING_INDEX(col, ' ', 1)
    ) FROM table;