From: | "Greg Sabino Mullane" <greg(at)turnstep(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Pattern matching |
Date: | 2007-10-21 12:45:32 |
Message-ID: | 38752a83d089deee4d46bb374c791818@biglumber.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
-----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-----
From | Date | Subject | |
---|---|---|---|
Next Message | Brian Hurt | 2007-10-22 14:46:58 | Query to detect long-running transactions? |
Previous Message | Dale Seaburg | 2007-10-21 01:23:06 | Pattern matching |