UPDATE statement to remove a space at the end

[mcmuney]

I'm looking for an update statement that will remove all the extra space from a specific field. For example, all data on this field are:

 

"Apple "

"Orange "

"Grapes Banana "

 

I'd like to update the database to remove the space at the end.

 

Thanks.

[Adam]

What data type are you using? MySQL varchars will have trailing spaces removed automatically.

[upp]

you could put those values in an array and use the str_replace function,

 

example:

$array = array('apple', 'orange', 'grapes banana');

 

$replace = '+'; // put what you want to put in the space here, example is a plus sign

$new = str_replace(' ', $replace, $array);

 

echo $new;

[Adam]

@upp

 

That's PHP, mcmuney requested a SQL update statement. Pus your code has a few problems with it, mainly what if the value had a space in the middle? You'd remove them all regardless of position. If you did want to do something like this in PHP you could just use trim, or rtrim if you only wanted to remove trailing spaces.

[Solaris]

You can also use the MySQL functions TRIM() (or RTRIM() for trailing spaces only) directly:

 

UPDATE `table` SET `field` = RTRIM(`field`);