Обсуждение: Pattern matching

I have an sql question.  I need to be able to search for text which
may have buried single-quotes.  I need to be able to effectively
ignore the single-quote in the WHERE clause.  Here is an example of
what a user might be looking for, but the data may have an embedded
single-quote:

PARKS PLACE ADDN

But, the data field may contain one of the two following entries,
both legitimate:

PARKS PLACE ADDN
PARK'S PLACE ADDN

What would be the best way to structure the WHERE clause to
effectively capture both types of records?

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

Regards,

Dale Seaburg

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