Mysql move characters around inside strings

Question

Just wondering if it is possible in Mysql to move characters inside a string without having to resort to PHP, etc.

Example:

Blue Dahlia, The

Has to become "The Blue Dahlia". So remove the comma and move the "the" after it to the beginning. Removing the comma could be done with:

... SET field = REPLACE(field, CHAR(47), '')

But moving the "the" to the beginning seems daunting to me since I just can't replace all the instances of "the", only the one after the last comma.

This is easily done with PHP but I was wondering if I could do it in SQL, since it's (hopefully) a one-time thing.

Thanks.

hsz · Accepted Answer · 2011-10-28 09:02:32Z

1

You can try with:

... SET field = CONCAT_WS(' ', TRIM(SUBSTRING_INDEX(field, ',', -1)), TRIM(SUBSTRING_INDEX(field, ',', 1)))

Quick explanation:

SUBSTRING_INDEX splits string by delimiter. -1 takes first part from the right; 1 first from the left.

TRIM removes unnecessary whitespaces.

CONCAT_WS merges parts of string with spacebar separator.

answered Oct 28, 2011 at 9:02

hsz

153k63 gold badges268 silver badges320 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wow, this actually works. I thought it would be impossible without an external scripting language. Thank you so much!