[SOLVED] Update RegEx in MySql

[MadTechie]

Not sure if it can be done, but i thought i'll check..

 

OK RegEx can be used with SELECT Statments on MySQL but Can i use it to UPDATE (something like PREG_REPLACE) i have searched around and don't think its supported but i am not 100% sure..

 

OK my System Config:

PHP Version 5.2.1

MySQL Client API version 4.1.22

posix

Revision $Revision: 1.70.2.3.2.12 $

 

 

If anymore System Info is needed just ask..

 

any info will help

 

Thanks in advance

 

--MadTechie

[Wildbug]

I believe it can.  Why don't you make a test case and find out?

[MadTechie]

i did attempt to but am getting no where, infact some of my selects still fails..

 

[Wildbug]

It works for me (v5.0.38).

 

UPDATE my_table SET firstname='David' WHERE lastname RLIKE 'ingston$';

 

Oh, wait a minute.  Like preg_REPLACE?  So you want to capture a parenthetical subpattern and use that in the SET value?  Oh.  No.  Probably not.

 

There's a replace() function, but it hardly has the power of regex.

 

If it's a one-time/infrequent thing, you could write a PHP (or other) script to suck out the data, submit it to a preg_replace, and spit it back in.  Otherwise, that wouldn't be efficient to do on a large set of data all the time.

[MadTechie]

yeah i started a script to do that..

i have tried to shorten the process using REGEXP but even then my more compleax RegEx don't seam to work well.. probably just a bad day. will try in the morning.

 

Thanx for all your help Wildbug