How can I replace certain strings in variable table all at once? I would like to replace all the 'aaaa' strings with 'bbbbbb' in value column of variable table.

I was initially trying to do that with just a simple query like this.

UPDATE variable SET `value` = replace(`value`,'aaaa','bbbbbb')


But this didn't work properly and I was taught that also the number between s: and :"  in value column has to be changed accordingly.

So variable_set seems to be the way to go but I'm not able to get it work.

https://api.drupal.org/api/drupal/includes%21bootstrap.inc/function/variable_set/6

How can I do this?
The variable holds serialized data, so you can't modify what you want with a simple query. You have you to get every variable value  with variable_get, process the value making the needed replacement and the save the variable using varaable_set.

You can get all vlaues with this snippet:

$variables = db_query('SELECT name FROM {variable}'); 

foreach ($variables as $variable_name) {
  $variable_value = variable_get($variable_name, NULL);
  $fixed_variable_value = your_replace_function($variable_value);
  variable_set($variable_name, $fixed_variable_value);
}


You will need the your_replace_function(). This function must deal with all possible values because $variable_value can be a number, a string, an array, object or any other PHP type. In case of arrays and object the function must process its nested components.