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

admin

Administrator
Staff member
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 :

Code:
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. :

Code:
"city name"

was actually inserted as

Code:
" city name "

of course , being a PHP person, I immediately thought of the
Code:
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

Code:
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
Code:
'meta_value'
and
Code:
`meta_value'
( sorry, markdown limitations seems to truncate the backticks)

Doing
Code:
replace(meta_value, ' ', '')
is dengaurous because some cities havea spaces in their name, and
Code:
LTRIM RTRIM
is somewhat not working for me as well

I also know there is a way to set a variable in sql (
Code:
@my_var
)
but what is the correct syntax of doing that in my case ?

Code:
SET @my_var = `meta_value`

and then

Code:
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..