Re: Pattern matching

-----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@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200710210842
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

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