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

On Thu, Apr 21, 2005 at 09:03:43AM -0400, David Gagnon wrote:
>
> 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

Have you considered using position() or strpos()?  They do simple
substring searches without any metacharacters.

SELECT position(lower('AbC') in lower('aBcDeF'));
 position
----------
        1
(1 row)

SELECT position(lower('xYz') in lower('aBcDeF'));
 position
----------
        0
(1 row)

You might also want to look at the contrib/pg_trgm module to see
if it would be useful.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/