Thread: Is it possible to search for sub-strings...
Ok, I have a question... lets say I have a key fields like this... JYR-34a JYR-34b JYR-34c JJG-67 jUY-89 I want to do a query on a substring, such that I can get all three of the JYR's to come up, regardless if it has an "a", "b", or "c" after it. Is that possible in SQL? If so, what would the query look like? John
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: > Ok, I have a question... lets say I have a key fields like this... > > JYR-34a > JYR-34b > JYR-34c > JJG-67 > jUY-89 > > I want to do a query on a substring, such that I can get all three > of the JYR's to come up, regardless if it has an "a", "b", or "c" > after it. Is that possible in SQL? If so, what would the query > look like? > > John > >
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:
> Ok, I have a question... lets say I have a key fields like this...
>
> JYR-34a
> JYR-34b
> JYR-34c
> JJG-67
> jUY-89
>
> I want to do a query on a substring, such that I can get all three
> of the JYR's to come up, regardless if it has an "a", "b", or "c"
> after it. Is that possible in SQL? If so, what would the query
> look like?
>
> John
>
>
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 > >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 >
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 ==========================================================================