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
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
