Skip site navigation (1) Skip section navigation (2)

Re: Pattern matching

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 (view raw or flat)
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-----



In response to

pgsql-novice by date

Next:From: Brian HurtDate: 2007-10-22 14:46:58
Subject: Query to detect long-running transactions?
Previous:From: Dale SeaburgDate: 2007-10-21 01:23:06
Subject: Pattern matching

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group