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