XF 1.5 Changing Custom User Field Data Via MySQL

[Divvens]

You can do this by running an SQL query that strips out the comma from the value, then convert the column to an int type

I have a custom user field that contains numbers and commas, I need to strip out all the commas from this custom user field and convert it to an integer type.

I'm not an expert when it comes to phpmyadmin and I don't want to spend lots of time doing this manually, what would be the query to run for such a task?

 

[Chris D]

If you go through with this, you certainly should be taking a backup of your database first, and be prepared to restore that backup in case anything goes wrong.

The query would be something like:

UPDATE xf_user_field_value
SET field_value = REPLACE(field_value, ',', '')
WHERE field_id = '???';

Change ??? to the ID of the custom field.

Once you have done this, you need to go to Admin CP > Tools > Rebuild Caches and rebuild all users - part of this process rebuilds the cached custom field values from the xf_user_field_value table.

 

[Azaly]

I've tried this query via phpMyAdmin script on 5.5-MariaDB (xf 2.1):

UPDATE xf_user_field_value
SET field_value = REPLACE(field_value, 'folk', '')
WHERE field_id = 'house';

But get an error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM xf_user_field_value WHERE field_id = 'house'' at line 2

Could someone point me to my mistake?