pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFinding strings inside a field...
6+ messages / 6 participants
[nested] [flat]
* Finding strings inside a field...
@ 1998-06-29 21:28 Dan Delaney <dionysos@dionysia.org>
0 siblings, 3 replies; 6+ messages in thread
From: Dan Delaney @ 1998-06-29 21:28 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] Finding strings inside a field...
@ 1998-06-29 21:36 Brett W. McCoy <bmccoy@lan2wan.com>
parent: Dan Delaney <dionysos@dionysia.org>
2 siblings, 0 replies; 6+ messages in thread
From: Brett W. McCoy @ 1998-06-29 21:36 UTC (permalink / raw)
To: Dan Delaney <dionysos@dionysia.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] Finding strings inside a field...
@ 1998-06-29 21:39 Tim J Trowbridge <trowbrid@execpc.com>
parent: Dan Delaney <dionysos@dionysia.org>
2 siblings, 0 replies; 6+ messages in thread
From: Tim J Trowbridge @ 1998-06-29 21:39 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] Finding strings inside a field...
@ 1998-06-29 22:10 James Olin Oden <joden@Lee.k12.nc.us>
parent: Dan Delaney <dionysos@dionysia.org>
2 siblings, 1 reply; 6+ messages in thread
From: James Olin Oden @ 1998-06-29 22:10 UTC (permalink / raw)
To: Dan Delaney <dionysos@dionysia.org>; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] Finding strings inside a field...
@ 1998-06-30 08:20 Zsolt Varga <redax@agria.hu>
parent: James Olin Oden <joden@Lee.k12.nc.us>
0 siblings, 0 replies; 6+ messages in thread
From: Zsolt Varga @ 1998-06-30 08:20 UTC (permalink / raw)
To: pgsql-sql
|
| 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/ |
`----------------------------------------------------------'
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] Finding strings inside a field...
@ 1998-07-15 06:34 Sejin Oh <turbo@hanwoolbbs.com>
0 siblings, 0 replies; 6+ messages in thread
From: Sejin Oh @ 1998-07-15 06:34 UTC (permalink / raw)
To: ¹Ú¹Ì·æ <mrpark@mail.tinc.co.kr>; pgsql-sql
create sequence table. and use nextval('sequence_name') when inserting.
eg:
create sequence autocounter;
create table customer (customerid int4 not null, name varchar(50) );
insert into customer values(nextval('autocounter),'Ronaldo');
or
create sequence autocounter;
create table customer (customerid int4 DEFAULT nextval('autocounter') not
null, name varchar(50) );
when you INSERT, just ignore customerid field.
ps: man create_sequence
hope this helped.
:)
-----¿øº» ¸Þ½ÃÁö-----
º¸³½ »ç¶÷: ¹Ú¹Ì·æ <mrpark@mail.tinc.co.kr>
¹Þ´Â »ç¶÷: pgsql-sql@postgreSQL.org <pgsql-sql@postgreSQL.org>
³¯Â¥: Tuesday, July 14, 1998 9:48 PM
Á¦¸ñ: Re: [SQL] Finding strings inside a field...
>How can i make the field auto incremental ?
>
>It is in the mysql, but That's not in pgsql.
>
>How can I ?
>
>Please give me a info.
>Thank you
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~1998-07-15 06:34 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-06-29 21:28 Finding strings inside a field... Dan Delaney <dionysos@dionysia.org>
1998-06-29 21:36 ` Brett W. McCoy <bmccoy@lan2wan.com>
1998-06-29 21:39 ` Tim J Trowbridge <trowbrid@execpc.com>
1998-06-29 22:10 ` James Olin Oden <joden@Lee.k12.nc.us>
1998-06-30 08:20 ` Zsolt Varga <redax@agria.hu>
1998-07-15 06:34 Re: [SQL] Finding strings inside a field... Sejin Oh <turbo@hanwoolbbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox