MySQL REPLACE string with regex

Question

I have a table with about 50,000 records. One of the fields is a "imploaded" field consisting of variable number of parameters from 1 to 800. I need to replace all parameters to 0. Example:

1 parameter 3.45 should become 0.00

2 parameters 2.27^11.03 should become 0.00^0.00

3 parameters 809.11^0.12^3334.25 should become 0.00^0.00^0.00

and so on.

Really I need to replace anything between ^ with 0.00 ( for 1 parameter it should be just 0.00 without ^).

Or I need somehow count number of ^, generate string like 0.00^0.00^0.00 ... and replace it. The only tool available is MySqlWorkbench.

I would appreciate any help.

Answer 1

There is no regex replace capability built in to MySQL.

You can, however, accomplish your purpose by doing what you suggested -- counting the number of ^ and crafting a string of replacement values, with this:

TRIM(TRAILING '^' FROM REPEAT('0.00^',(LENGTH(column) - LENGTH(REPLACE(column,'^','')) + 1)));

From inside to outside, we calculate the number of values by counting the number of delimiters, and adding 1 to that count. We count the delimiters by comparing the length of the original string, against the length of the same string with the delimiters stripped out using REPLACE(...,'^','') to replace every ^ with nothing.

The REPEAT() function builds a string by repeating a string expression n number of times.

This results in a spurious ^ at the end of the string, which we remove easily enough with TRIM(TRAILING '^' FROM ...).

SELECT t1.*, ... the expression above ... FROM table_name t1, from your table to verify the results of this logic (replacing column with the actual name of the column), then you can UPDATE table SET column = ... to modify the values. once you are confident in the logic.

Note, of course, that this is indicative of a problematic database design. Each column should contain a single atomic value, not a "list" of values, as this question seems to suggest.