Re: Pattern matching

["Greg Sabino Mullane" <greg(at)turnstep(dot)com>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> I have an sql question.  I need to be able to search for text which  
> may have buried single-quotes.  

SELECT * FROM foo WHERE REPLACE(baz,$$'$$,'') = 'PARKS PLACE ADDN';

If you plan on doing this often, you should create a functional index 
on the replacement:

CREATE INDEX foobar ON foo(REPLACE(baz,$$'$$,''));

> The more general question would be, "How to structure a query to  
> ignore certain characters?"

You might also want to consider using tsearch2, since breaking words 
down into its constituent parts is one of the things it does well:

greg=# SELECT to_tsvector('PARK''S PLACE ADDN');
         to_tsvector         
- -----------------------------
 'ADDN':4 'PARK':1 'PLACE':3

greg=# SELECT to_tsvector('PARKS PLACE ADDN');
         to_tsvector         
- -----------------------------
 'ADDN':3 'PARK':1 'PLACE':2

Using tsearch2 may be overkill however: if you really just need 
to ignore a few characters, the functional index and modified 
where clause above should be fine.


- --
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation
PGP Key: 0x14964AC8 200710210842
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFHG0mwvJuQZxSWSsgRAyUZAKDLreE7yEZBbnRlwaQIZLtmU8rrqwCg1xT0
tyXw4JwfsZS5weuVRQAkcMc=
=Ag7l
-----END PGP SIGNATURE-----