List:General Discussion« Previous MessageNext Message »
From:(Hal Date:March 12 2012 7:14pm
Subject:Re: preg_replace in update statement
View as plain text  
>>>> 2012/03/08 16:11 -0500, Hank >>>>
I have a varchar field in the database, and I want to remove all text
between WordA and WordB, including WordA and WordB, leaving all text
before WordA and after WordB intact.

Possible with just SQL?  I know I can write a PHP program to do it,
but it's not that important to spend that much time on.  I'd like one
SQL statement to do it.

One statement is hard, but maybe you'll take an SQL function?

IF field REGEXP (WordA || '.*' || WordB) THEN
	SET i = LOCATE(WordA, field),
	j = LOCATE(REVERSE(WordB), REVERSE(field));
	SET answer = LEFT(field, i-1) || RIGHT(field, j-1);
ELSE	SET answer = field;

or something like that (ANSI mode).

It would be much nicer to get a location pair out of REGEXP (RLIKE) than only a yes/no
(1/0). In this case, the most useful _one_ number from REGEXP would be the length of the

preg_replace in update statementHank8 Mar
  • Re: preg_replace in update statementBaron Schwartz9 Mar
    • RE: preg_replace in update statementDavid Lerer9 Mar
  • Re: preg_replace in update statementhsv10 Mar
Re: preg_replace in update statementHank9 Mar