UPDATE space to _

mysql

[ukgimp]

Is there a way to update a whole table with and underscore where a space occurs. In access it can be done with

UPDATE [table] SET feild= replace(feild,' ','_');

Is there a similar one for mysql. This is critical data so I dont wish to just try it out and see how it goes.

cheers

[jatar_k]

There is a REPLACE function,
MySQL String Functions [mysql.com]

I think the usage is very similar

[coopster]

Exactly. I don't think you can SET a variable yet. It is listed in the New Features Planned for the Near Future [mysql.com] (fifth bullet down from the top):

Allow update of variables in UPDATE statements. For example: UPDATE TABLE foo SET @a=a+b,a=@a, b=@a+c.

[ukgimp]

This one looks like what I was thinking of

REPLACE(str,from_str,to_str)
Returns the string str with all occurrences of the string from_str replaced by the string to_str:
mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'

[coopster]

I'm sorry ukgimp, I read the thread and for some reason thought you were trying to update a variable you had SET. Post #3 is in regards to updating USER variables, completely different from what you want to do:

update table SET field=replace(field, ' ', '_');

I must be overly tired, better stay off the boards today ;)