Mysql Regex Search and Replace not updating records

Question

I have a MYSQL database that needs some data removed from a field.

My current data is like this..

gttp://www.somesite.com?ref=1234567
gttp//notquitealink.com/ref.php?r=myreferral

I am trying to remove the gttp://www.somesite.com?ref= to leave only the last part after the =

so I want the data to look like this

1234567
myreferral

I found this code from a previous question. MySQL Regex Search and replace

DELIMITER $$
CREATE FUNCTION  `regex_replace`(pattern VARCHAR(1000),replacement VARCHAR(1000),original VARCHAR(1000))

RETURNS VARCHAR(1000)
DETERMINISTIC
BEGIN 
 DECLARE temp VARCHAR(1000); 
 DECLARE ch VARCHAR(1); 
 DECLARE i INT;
 SET i = 1;
 SET temp = '';
 IF original REGEXP pattern THEN 
  loop_label: LOOP 
   IF i>CHAR_LENGTH(original) THEN
    LEAVE loop_label;  
   END IF;
   SET ch = SUBSTRING(original,i,1);
   IF NOT ch REGEXP pattern THEN
    SET temp = CONCAT(temp,ch);
   ELSE
    SET temp = CONCAT(temp,replacement);
   END IF;
   SET i=i+1;
  END LOOP;
 ELSE
  SET temp = original;
 END IF;
 RETURN temp;
END$$
DELIMITER ;

I ran it and it created a function that I could query on.

When I run this query....

UPDATE `tb_te_builder_user` SET tebu_refID = regex_replace('gttp(.*)=', '',tebu_refID);

I get 0 rows affected. (Query took 10.9480 sec)

But when I try to do a select

SELECT * FROM `tb_te_builder_user` WHERE tebu_refID REGEXP 'gttp(.*)='

I get well over 7,000 records returned.

I am not quite sure what to make of it... I think I am missing something very simple here.

I was also looking at this solution https://github.com/mysqludf/lib_mysqludf_preg#readme but I don't have rights to use the make command on my server. So I am left with trying to figure out what I am doing incorrectly.

Any help is appreciated.

@Buster this selects only 5 records... out of 7000 all of them end with w — David Eaton, Dec 22 '14 at 22:43
Sorry I cant test right now, but i will look into it tomorrow — Buster, Dec 22 '14 at 22:52
SELECT * FROM `tb_te_builder_user` WHERE tebu_refID REGEXP '.*\=(.+)' — Buster, Dec 22 '14 at 23:01

Julian Ladisch · Answer 1 · 2014-12-22T23:28:11.123

0

That function only works for a single character pattern. This limitation is mentioned at the source site.

For a string pattern you may use a user defined function, eg. mysql-udf-regexp, see How to do a regular expression replace in MySQL?. However, to install a user defined function you need to compile it and add it to the MySQL installation.

MariaDB, a MySQL drop-in replacement, has a builtin function regex_replace fully supporting string patterns.

If you cannot do any of these you may try an external solution, eg. using PHP and PHP's preg_replace function.

edited Dec 22 '14 at 23:28

answered Dec 22 '14 at 23:03

Julian Ladisch

1,367
9
10

1

I guess I missed that it's a simple char pattern. Now mysql-udf-regexp is this something I have to install? I don't have access to make on my server. MariaDB is out of the question also.. The only access I have to Mysql is PHPMyadmin. Unless I am missing something ? – David Eaton Dec 22 '14 at 23:18

Mysql Regex Search and Replace not updating records

1 Answers1