Replace a column value in a table using regex in mysql

Question

I need to do the following and I'm struggling with the syntax:

I have a table called 'mytable' and a column called 'mycolumn' (string).

In mycolumn the value is a constructed value - for example, one of the values is: 'first:10:second:18:third:31'. The values in mycolumn are all using the same pattern, just the ids/numbers are different.

I need to change the value of 18 (in this particular case) to a value from another tables key. The end result for this column value should be 'first:10:second:22:third:31' because I replaced 18 with 22. I got the 22 from another table using the 18 as a lookup value.

So ideally I would have the following:

UPDATE mytable 
SET mycolumn = [some regex function to find the number between 'second:' and ":third" - 
let's call that oldkey - and replace it with other id from another table - 
(select otherid from tableb where id = oldkey)].

I know the mysql has a REPLACE function but that doesn't get me far enough.

Why are you storing data in these delimited strings? Can't you break them up into their own columns? — eggyal, Jun 29 '12 at 18:05
yes, I agree, but I'm dealing with some legacy in the database. — Arthur Frankel, Jun 29 '12 at 18:13

Alex W · Answer 1 · 2012-06-29T18:46:00.573

1

You want something like this, where it matches the group:

WHERE REGEXP 'second:([0-9]*):third'

However, MySQL doesn't have a regex replace function, so you would have to use a user-defined function:

REGEXP_REPLACE?(text, pattern, replace [,position [,occurence [,return_end [,mode]]])

User-defined function is available here:

http://www.mysqludf.org/lib_mysqludf_preg/

edited Jun 29 '12 at 18:46

answered Jun 29 '12 at 18:06

Alex W

37,233
13
109
109

I think this is getting me closer (maybe?) but really what I want is to find those digits and replace them. Something like this simple example (where I just replace the number with 16...but this doesn't work because REGEX is just returning 1 or 0 - true or false for a match): SELECT REPLACE(mycolumn, mycolumn REGEXP 'second:([0-9]*):third', '16') from discussions; – Arthur Frankel Jun 29 '12 at 18:15
@ArthurFrankel Here you go, https://launchpad.net/mysql-udf-regexp . It's a user-defined REGEX replace function. – Alex W Jun 29 '12 at 18:22
Here's another one that seems easier to install: http://www.mysqludf.org/lib_mysqludf_preg/ – Alex W Jun 29 '12 at 18:45
Thanks, I decided to go with @AllInOne answer because I would have to jump through hoops to get this function on the production boxes for this migration script. For me it was easier to have a basic function in line. Thanks for your help. – Arthur Frankel Jun 29 '12 at 18:51

score 1 · Answer 2 · answered Jun 29 '12 at 18:10

The problem with MySQL is it's REGEX flavor is very limited and does not support back references or regex replace, which pretty much makes it impossible to replace the value like you want to with MySQL alone.

I know it means taking a speed hit, but you may want to consider selecting the row you want with by it's id or however you select it, modify the value with PHP or whatever language you have interfacing with MySQL and put it back in with an UPDATE query.

Generally speaking, REGEX in programming languages is much more powerful.

If you keep those queries slim and quick, you shouldn't take too big of a speed hit (probably negligible).

Also, here is documentation on what MySQL's REGEX CAN do. http://dev.mysql.com/doc/refman/5.1/en/regexp.html

Cheers

EDIT:

To be honest, eggyal's comment makes a whole lot more sense for your situation (simple int values). Just break them up into columns there's no reason to access them like that at all imo.

It's part legacy, but really the value of that column is used as is as a key for an external system so keeping it as one field is somewhat best (performance). Thanks for your answer. — Arthur Frankel, Jun 29 '12 at 18:52

score 1 · Accepted Answer · edited May 23 '17 at 10:34

1

You can create your own function. I am scared of REGEX so I use SUBSTRING and SUBSTRING_INDEX.

CREATE FUNCTION SPLIT_STRING(str VARCHAR(255), delim VARCHAR(12), pos INT)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),
   LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1),
   delim, '');

SPLIT_STRING('first:10:second:18:third:31', ':', 4)

returns 18

Based on this answer:

Equivalent of explode() to work with strings in MySQL

edited May 23 '17 at 10:34

Community

1
1

answered Jun 29 '12 at 18:25

AllInOne

1,450
2
14
32

Thank you. I decided to go with your answer and use it with the CONCAT function in mysql to recreate this composite string. – Arthur Frankel Jun 29 '12 at 18:50

Replace a column value in a table using regex in mysql

3 Answers3