-----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-----