Postgres Pro
Downloads
Home   >   mailing lists

Thread: Finding strings inside a field...

Finding strings inside a field...

From
Dan Delaney
Date:
Hello all.
   Could any one tell me if the following is possible using
PostgreSQL?

I want to be able do a SELECT and search for a string INSIDE OF a
field. So, say I want to get all record in testtable which contain
the word "foobar" inside the "description" attribute. The following
will only get those records in which the "description" attribute
contains ONLY the word "foobar":

SELECT FROM testtable WHERE description='foobar';

What do I need to do to get all the record that have "foobar"
ANYWHERE within the "description" field?
   Thanks a lot.
 --Dan

-----------------------------------------------------------------------
 Daniel G. Delaney                    The Louisville Times Chorus
 Dionysos@Dionysia.org                   www.LouisvilleTimes.org
 www.Dionysia.org/~dionysos/          Dionysia Design
 ICQ Number: 8171285                     www.Dionysia.com/design/
-----------------------------------------------------------------------
                   I doubt, therefore I might be.

Re: [SQL] Finding strings inside a field...

From
"Tim J Trowbridge"
Date:
Dan Delaney wrote:
>
> Hello all.
>    Could any one tell me if the following is possible using
> PostgreSQL?
>
> I want to be able do a SELECT and search for a string INSIDE OF a
> field. So, say I want to get all record in testtable which contain
> the word "foobar" inside the "description" attribute. The following
> will only get those records in which the "description" attribute
> contains ONLY the word "foobar":
>
> SELECT FROM testtable WHERE description='foobar';

Try this:

SELECT FROM testtable WHERE description like '%foobar%';

Note that this isn't case insensitive.



--
Tim J Trowbridge                         trowbrid@writeme.com
Interested in my genealogy?          http://www.execpc.com/~trowbrid

Re: [SQL] Finding strings inside a field...

From
"Brett W. McCoy"
Date:
On Mon, 29 Jun 1998, Dan Delaney wrote:

> I want to be able do a SELECT and search for a string INSIDE OF a
> field. So, say I want to get all record in testtable which contain
> the word "foobar" inside the "description" attribute. The following
> will only get those records in which the "description" attribute
> contains ONLY the word "foobar":
>
> SELECT FROM testtable WHERE description='foobar';
>
> What do I need to do to get all the record that have "foobar"
> ANYWHERE within the "description" field?

You'll want to use the LIKE operator, with '%' and '_' used as wildcard
operators similar to '*' and '?' used in the Unix CLI.

SELECT * from MyDB WHERE DESCRIPTION LIKE "%foobar%";

Brett W. McCoy
                                        http://www.lan2wan.com/~bmccoy/
-----------------------------------------------------------------------
"The number of UNIX installations has grown to 10, with more expected."
   -- The UNIX Programmer's Manual, 2nd Edition, June, 1972

Re: [SQL] Finding strings inside a field...

From
James Olin Oden
Date:
> Hello all.
>    Could any one tell me if the following is possible using
> PostgreSQL?
>
> I want to be able do a SELECT and search for a string INSIDE OF a
> field. So, say I want to get all record in testtable which contain
> the word "foobar" inside the "description" attribute. The following
> will only get those records in which the "description" attribute
> contains ONLY the word "foobar":
>
> SELECT FROM testtable WHERE description='foobar';
>
> What do I need to do to get all the record that have "foobar"
> ANYWHERE within the "description" field?
>    Thanks a lot.
>  --Dan
>

The answer is to use the LIKE operator along with the wildcard character
'%' (why they did not use * I will never know).  Anyway, say you wanted to
find all streets that contained 'Zion' then you would write something like
(no pun intended):

   SELECT * FROM streets WHERE street LIKE '%Zion%';

And as long as you have your handy dandy Hebrew translator, Zion would be
come Tziyon...just kidding, you would get all the streets that contain
'Zion' in there name.

Hope this helps...james

Re: [SQL] Finding strings inside a field...

From
Zsolt Varga
Date:
|
|   SELECT * FROM streets WHERE street LIKE '%Zion%';
|
or even
    SELECT * FROM streets WHERE street ~ 'zion';
    SELECT * FROM streets WHERE street ~* 'ZiOn';

the second one is case insensitive....

    redax

.----------------------------------------------------------.
|Zsolt Varga            | tel/fax:   +36 36 422811         |
| AgriaComputer LTD     | email:     redax@agria.hu        |
| System Administrator  | URL:       http://www.agria.hu/  |
`----------------------------------------------------------'