MySQL, moving first word to last

Question

How would 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

Answer 1

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.

Answer 2

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;

Answer 3

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.

Answer 4

Hi awgy,

Small correction in your query, but nice result.

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

Answer 5

Bob, your answer worked a charm, apart from the fact that I ran it on the wrong field (it was late, I'd had a long day, etc, etc)

Can you tell me how to reverse this, please? I've had a go, and can move the 'The' back to the front, but can't figure out how to get rid of the trailing ', The'