Re: Using POSIX Regular Expressions on xml type fields gives inconsistent results

>>
>> "David Johnston" <polobo(at)yahoo(dot)com> writes:
>> > [optionally go look at " ~* " in the documentation at this point; or just
>> > try a simple flip-flop of the expression]
>>
>> > SELECT 'Kumar' ~* 'kuma' -> TRUE !!! (ah ha! I had the order reversed)
>>
>> > SELECT any( ARRAY['Kumar','Gozales']::text ) ... wait...ANY doesn't work on
>> > the other side... :(
>>
>> > [At this point I'd confirm or question why ANY hasn't been made to go both
>> > ways but also realize that I will have to approach this in a different way
>> > to achieve my goal.]
>>
>> It's been awhile since I looked at the point in detail, but I seem to
>> recall that there are fundamental syntactic-ambiguity reasons why the
>> ANY/ALL part has to be on the righthand side of the comparison operator.
>>
>> There's a much easier fix to this problem though, which is to invent a
>> "reverse ~" operator that does POSIX comparison with the pattern on the
>> left. The hardest part of doing that for yourself is choosing a name
>> for the reverse operator --- it just goes like
>>
>> create function reverse_regexeq(text, text) returns bool as
>> 'select $2 ~ $1' language sql strict immutable;
>>
>> create operator ~~~ (procedure = reverse_regexeq,
>> leftarg = text, rightarg = text);
>>
>> and similarly for the case-insensitive version, and there you go:
>> pattern ~~~ ANY (whatever) solves the problem.
>>

Hi Tom,

while it might work to reverse the order in a regex, I have experienced severe slowdown
when the pattern comes from table data

Regards
Wolfgang Hamann