Re: Regular expression. How to disable ALL meta-character

Thanks for your help!

Just want to share the solution I got to solve my problem. I wanted to
be eable to search a string (say X) (non case sensitive) without having
meta-character involved. The X string come directy from the web so any
[%]* may cause error in regular expression (because they form non valid
expression)

1) Using like: select * from mytable where lower(mycol) LIKE lower("%"
|| lower(X) || "%");
Mostly perfect solution. Don't crash but % still have a special
meaning. Wich means anything
2)Using regular expression: select * from mytable where mycol ~*
('***=' || X)
For the test I did it doesn't, fit all my need. No meta character and
no escaping to do on X before launching the SQL request.

Thanks for your help!!!
Have a great day
/David

Chris Travers wrote:

> David Gagnon wrote:
>
>>
>>>
>>>
>>>
>>>
>> Maybe there is a simple way to to this but I want find string X in
>> different column. The search must not be case sensitive.
>>
>> So that searching "aBc" in "abcDef" return true. I don't want
>> META-CHaracter. Or at least I don't want meta-character to cause
>> errors (i.e.: No
>>
> Ok, how about a better way to do this?
>
> select * from mytable where lower(mycol) LIKE lower("%" || lower(X) ||
> "%");
>
> Does this work? It seems that this may be the best way to handle this
> sort of thing.
>
> Best Wishes,
> Chris Travers
> Metatron Technology Consulting
>
>> ERROR: invalid regular expression: brackets [] not balanced.
>>
>>
>>
>> Thanks for your help
>> /David
>>
>>
>>
>>
>>
>>
>>
>>
>>>> I found this in the manual .. but haven't found example :-(:
>>>> : ....with ***=, the rest of the RE is taken to be a literal
>>>> string, with all characters considered ordinary characters.
>>>>
>>>
>>>
>>>
>>> Read again the entire sentence, especially the first few words:
>>>
>>> If an RE begins with ***=, the rest of the RE is taken to be a
>>> literal string, with all characters considered ordinary characters.
>>>
>>> Here are some examples:
>>>
>>> SELECT 'test string' ~ 'test[*';
>>> ERROR: invalid regular expression: brackets [] not balanced
>>>
>>> SELECT 'test string' ~ '***=test[*';
>>> ?column? ----------
>>> f
>>> (1 row)
>>>
>>> SELECT 'test[* string' ~ '***=test[*';
>>> ?column? ----------
>>> t
>>> (1 row)
>>>
>>>
>>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 7: don't forget to increase your free space map settings
>>
>>
>