Query to REGEX_REPLACE in mysql as per another column

Question

I have a table T with two columns C1 and C2.

I want to write a query as follows:

UPDATE TABLE T 
SET C2 = REGEX_REPLACE(
    "(REG_SUB_PART1)(REG_SUB_PART2)(REG_SUB_PART3)", 
    C1, 
    REG_SUB_PART1
) 
WHERE C2="ABC";

Effectively, I want to use another column C1, let's say URL "http://www.google.com" and set C2 to be a part of it, let's say "google.com" using $3 (third part) of regex "(http://)?(www\.)?([a-zA-Z0-9]*)".

As a result, C2 should be set as "google.com".

How could it be done using MySql?

PS: Please don't concentrate on specific regex.

Binita Mehta · Accepted Answer · 2013-11-20 14:09:19Z

1

There is no way to do regex capture groups in MySQL per http://dev.mysql.com/doc/refman/5.0/en/regexp.html. However, if you know the starting index in c1, an alternate way to accomplish a similar thing would be.

update t set c2= substring(c1,12) where c2 = 'abc';

This will get the substring 'google.com' from 'http://www.google.com', provided that was the value in column c1.

Some other MySQL string functions that might come in handy are substring, substring_index and locate.

answered Nov 20, 2013 at 14:09

Binita Mehta

3391 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query to REGEX_REPLACE in mysql as per another column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related