SQL TRIM all values in specific field ( and variable set )

[admin]

Excuse me if I ask the obvious, but I am quite mySQL illiterate .

I am referring to a standard wordpress DB install, although this is not a wordpress specific question, but more mySQL general knowledge ..

If I want to change all the values of a certain field across all posts , I usually do :

UPDATE `wp_postmeta` SET `meta_value` = replace(meta_value, 'old_value', 'new_value') WHERE `meta_key` LIKE 'my_meta_key'

The problem is as follows :

In that specific DB , the INITIAL values were inserted with a white space prefix and suffix , e.g. :

"city name"

was actually inserted as

" city name "

of course , being a PHP person, I immediately thought of the

trim()

command - which I found out exists <a href="http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim" rel="nofollow">also in mySQL</a>.

SO now I thought I could just do

UPDATE `wp_postmeta` SET `meta_value` = TRIM('meta_value') WHERE `meta_key` LIKE 'my_meta_key'

But It is not working ..

What Am I doing wrong ? I know that mySQL is "ticks sensetive" ( so to speak ) But I tried both

'meta_value'

and

`meta_value'

( sorry, markdown limitations seems to truncate the backticks)

Doing

replace(meta_value, ' ', '')

is dengaurous because some cities havea spaces in their name, and

LTRIM RTRIM

is somewhat not working for me as well

I also know there is a way to set a variable in sql (

@my_var

)
but what is the correct syntax of doing that in my case ?

SET @my_var = `meta_value`

and then

UPDATE `wp_postmeta` TRIM(@my_var) WHERE `meta_key` LIKE 'my_meta_key'

I of course know how to fix it in a php loop (with trim() ) but I would like to learn what is the best way to do it in mySQL..