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;
END IF

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
match.

Thread
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