Thread: Syntax for wildcard selection
This question just came up from a user use to our Informix application. They tried to do a wildcard search, thus "where field_name LIKE 'AB%VN'". The trailing values (after the %) are not recognized correctly. With Informix 4GL, we wrote "where field_name MATCHES 'AB*VN'". This finds any combination of values with 'AB' as the first two characters, and 'VN' as the last two, with any number of characters in between - including blanks. How is this accomplished with PostgreSQL? Are we limited to wildcard searches as "where field_name LIKE 'AB%'"?
[snip:Informix trouble]
> tried to do a wildcard search, thus "where field_name LIKE 'AB%VN'". The
> trailing values (after the %) are not recognized correctly.
[...]
> How is this accomplished with PostgreSQL? Are we limited to wildcard
> searches as "where field_name LIKE 'AB%'"?
Well, it's hardly a conclusive proof, but it works the way you'd hope
on a toy problem....
foo=> DROP TABLE test;
DROP
foo=> CREATE TABLE test (name varchar(20),age int);
CREATE
foo=> INSERT INTO test VALUES ('AGNES', 20);
INSERT 586226 1
foo=> INSERT INTO test VALUES ('HELMUT', 33);
INSERT 586227 1
foo=> INSERT INTO test VALUES ('ANDREW', 33);
INSERT 586228 1
foo=> INSERT INTO test VALUES ('AGNEW', 302);
INSERT 586229 1
foo=> SELECT * FROM test WHERE name LIKE 'AG%ES';
name | age
-------+-----
AGNES | 20
(1 row)
foo=> SELECT * FROM test WHERE name LIKE 'AG%E';
name | age
------+-----
(0 rows)
foo=> SELECT * FROM test WHERE name LIKE 'AG%EW';
name | age
-------+-----
AGNEW | 302
(1 row)
Someone see anything I'm missing?
Jason
--
Indigo Industrial Controls Ltd.
64-21-343-545
jasont@indigoindustrial.co.nz
On Wed, Aug 15, 2001 at 04:06:16PM -0700, Scott Holmes wrote: > This question just came up from a user use to our Informix application. They > tried to do a wildcard search, thus "where field_name LIKE 'AB%VN'". The > trailing values (after the %) are not recognized correctly. With Informix > 4GL, we wrote "where field_name MATCHES 'AB*VN'". This finds any combination > of values with 'AB' as the first two characters, and 'VN' as the last two, > with any number of characters in between - including blanks. How is this > accomplished with PostgreSQL? Are we limited to wildcard searches as "where > field_name LIKE 'AB%'"? The only thing I can think of is that you are using char() fields and the like is getting confused by the trailing spaces. Certainly putting wildcards anywhere in the string works fine. What is the data type of your column? text and varchar() wouldn't suffer from the above problem. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > It would be nice if someone came up with a certification system that > actually separated those who can barely regurgitate what they crammed over > the last few weeks from those who command secret ninja networking powers.
> This question just came up from a user use to our Informix application. They > tried to do a wildcard search, thus "where field_name LIKE 'AB%VN'". The > trailing values (after the %) are not recognized correctly. With Informix > 4GL, we wrote "where field_name MATCHES 'AB*VN'". This finds any combination > of values with 'AB' as the first two characters, and 'VN' as the last two, > with any number of characters in between - including blanks. How is this > accomplished with PostgreSQL? Are we limited to wildcard searches as "where > field_name LIKE 'AB%'"? Trailing stuff should always be recognized, and I am sure PostgreSQL does this. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian wrote:
>
> > This question just came up from a user use to our Informix application. They
> > tried to do a wildcard search, thus "where field_name LIKE 'AB%VN'". The
> > trailing values (after the %) are not recognized correctly. With Informix
> > 4GL, we wrote "where field_name MATCHES 'AB*VN'". This finds any combination
> > of values with 'AB' as the first two characters, and 'VN' as the last two,
> > with any number of characters in between - including blanks. How is this
> > accomplished with PostgreSQL? Are we limited to wildcard searches as "where
> > field_name LIKE 'AB%'"?
>
> Trailing stuff should always be recognized, and I am sure PostgreSQL
> does this.
>
Perhaps regular expression may help you out...
WHERE field_name ~ '^AB' AND field_name ~ 'VN$'
You should also only process those that have greater than four
characters in the field_name...;-)
Hope that helps...;-)
Cheers,
John Clark
--
/) John Clark Naldoza y Lopez (\
/ ) Software Design Engineer III ( \
_( (_ _ Web-Application Development _) )_
(((\ \> /_> Cable Modem Network Management System <_\ </ /)))
(\\\\ \_/ / NEC Telecom Software Phils., Inc. \ \_/ ////)
\ / \ /
\ _/ phone: (+63 32) 233-9142 loc. 3113 \_ /
/ / cellphone: (+63 919) 399-4742 \ \
/ / email: njclark@ntsp.nec.co.jp \ \
"Intelligence is the ability to avoid doing work, yet getting the work
done"
--Linus Torvalds
Minor regex suggestion... > Perhaps regular expression may help you out... > > WHERE field_name ~ '^AB' AND field_name ~ 'VN$' That will still skip the records with trailing blanks. WHERE field_name ~ '^AB.*VN[ \t\r\n]*$' won't. Jason -- Indigo Industrial Controls Ltd. 64-21-343-545 jasont@indigoindustrial.co.nz
It would seem that my database has unseen garbage in the field being queried. On further testing I find that select * from people where peopcode LIKE 'AB%AH%' order by peopcode; works, however select * from people where peopcode LIKE 'AB%AH' order by peopcode; does not. I do have nine records that meet the above criteria and are found if the pattern ends with '%' but not without it.
The field is, indeed, a char(17) field. This particular database is actually a copy of the same schema we use in our Informix applications. The definitions for that system were almost completely correct for creating the PostgreSQL version, thus many fields are defined as char(x). I shall try redefining those fields that are of variable length as varchar() and see what happens. Thanks... > The only thing I can think of is that you are using char() fields and the > like is getting confused by the trailing spaces. Certainly putting wildcards > anywhere in the string works fine. > > What is the data type of your column? text and varchar() wouldn't suffer > from the above problem. > -- > Martijn van Oosterhout <kleptog@svana.org>
On Wed, Aug 15, 2001 at 05:11:28PM -0700, Scott Holmes wrote:
> It would seem that my database has unseen garbage in the field being queried.
> On further testing I find that
>
> select *
> from people
> where peopcode LIKE 'AB%AH%'
> order by peopcode;
>
> works, however
>
> select *
> from people
> where peopcode LIKE 'AB%AH'
> order by peopcode;
>
> does not. I do have nine records that meet the above criteria and are found
> if the pattern ends with '%' but not without it.
here's my guess --
you probably used
create table people (
...
peopcode CHAR(...)
...
);
instead of
peopcode VARCHAR(...)
the "char" type pads with blanks (ascii 32 in latin1 encoding)
to fill the size of the field. "varchar" truncates the data
only if the data exceeds the field size.
so
like "%pat"
will match fields ENDING with "pat" -- for varchar that's
usually at the end of the data; for char, that's at the last few
characters as speficied by the length of the field.
thus, for a field containing
"stuff like this "
a LIKE "%this" would not match, but a LIKE "%this%" would, and so
would LIKE "%this ".
--
Khan said that revenge is a dish best served cold. I think
sometimes it's best served hot, chunky, and foaming.
- P.J.Lee ('79-'80)
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!