Is it possible to search for sub-strings...

Assuming that the name of the table is "test1" and the variable containing
the values of interest is name "values", then:

SELECT * FROM test1 WHERE values LIKE 'JYR%';

On Tue, 19 Sep 2000, John Draper wrote:

I find this useful where you have mixed case:

SELECT * FROM test1 WHERE values ~* 'JYR';

Andrew
----- Forwarded by Andrew Mason/Spektra on 19/09/00 11:38 -----

John McKown <joarmc@swbell.net>
Sent by: pgsql-general-owner@hub.org
19/09/00 11:27

To: John Draper <crunch@webcrunchers.com>
cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Is it possible to search for sub-strings...

Assuming that the name of the table is "test1" and the variable containing
the values of interest is name "values", then:

SELECT * FROM test1 WHERE values LIKE 'JYR%';

On Tue, 19 Sep 2000, John Draper wrote:

At 05:27 19/09/00 -0500, John McKown wrote:

Assuming that the name of the table is "test1" and the variable containing
the values of interest is name "values", then:

SELECT * FROM test1 WHERE values LIKE 'JYR%';

or
select * from test1 where values ~ '^JYR';

or for case insensitive:
select * from test1 where values ~* '^JYR';

Steve

--
thorNET - Internet Consultancy, Services & Training
Phone: 01454 854413
Fax: 01454 854412
http://www.thornet.co.uk

LIKE can use indexes, ~* can not, that's LIKE's main advantage (at least in
my opinion).

-Mitch

----- Original Message -----
From: "Steve Heaven" <steve@thornet.co.uk>
To: "John Draper" <crunch@webcrunchers.com>
Cc: <pgsql-general@postgresql.org>
Sent: Tuesday, September 19, 2000 3:44 AM
Subject: Re: [GENERAL] Is it possible to search for sub-strings...

At 05:27 19/09/00 -0500, John McKown wrote:

Assuming that the name of the table is "test1" and the variable

containing

At 09:00 19/09/00 -0700, Mitch Vincent wrote:

LIKE can use indexes, ~* can not, that's LIKE's main advantage (at least in
my opinion).

~ does use indexes, ~* doesnt,
but then nor does the equivalent upper(columnname) LIKE 'TERM'. Make sure
you're comparing apples with apples.

explain select * from all_title_fti where string like 'A%';
NOTICE: QUERY PLAN:

Index Scan using all_title_idx on all_title_fti (cost=86633.57 rows=1
width=16)

explain select * from all_title_fti where string ~ '^A';
NOTICE: QUERY PLAN:

Index Scan using all_title_idx on all_title_fti (cost=86633.57 rows=1
width=16

explain select * from all_title_fti where upper(string) like 'A%';
NOTICE: QUERY PLAN:

Seq Scan on all_title_fti (cost=170921.58 rows=1083414 width=16)
--
thorNET - Internet Consultancy, Services & Training
Phone: 01454 854413
Fax: 01454 854412
http://www.thornet.co.uk

LIKE can use indexes, ~* can not, that's LIKE's main advantage (at least

in

my opinion).

~ does use indexes, ~* doesnt,
but then nor does the equivalent upper(columnname) LIKE 'TERM'. Make sure
you're comparing apples with apples.

Indeed, all that's true but did I say otherwise? What I said was true, just
not as detailed as yours.. Apples to apples though, the origional poster
just wanted to search for substrings and said nothing about case.

No big deal, just clearing that up.

-Mitch

On Tue, 19 Sep 2000, Steve Heaven wrote:

At 09:00 19/09/00 -0700, Mitch Vincent wrote:

LIKE can use indexes, ~* can not, that's LIKE's main advantage (at least in
my opinion).

~ does use indexes, ~* doesnt,
but then nor does the equivalent upper(columnname) LIKE 'TERM'. Make sure
you're comparing apples with apples.

It can:

campsites=> explain select * from camps4 where upper(city) like 'MACKINA%';
NOTICE: QUERY PLAN:

Index Scan using camps4_ucity on camps4 (cost=106.34 size=644 width=132)

In my particular case the like search needed to perform an upper() like
select, an index was created for this purpose (in this case: camps4_ucity)

Vince.

explain select * from all_title_fti where string like 'A%';
NOTICE: QUERY PLAN:

Index Scan using all_title_idx on all_title_fti (cost=86633.57 rows=1
width=16)

explain select * from all_title_fti where string ~ '^A';
NOTICE: QUERY PLAN:

Index Scan using all_title_idx on all_title_fti (cost=86633.57 rows=1
width=16

explain select * from all_title_fti where upper(string) like 'A%';
NOTICE: QUERY PLAN:

Seq Scan on all_title_fti (cost=170921.58 rows=1083414 width=16)

--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================