Syntax for wildcard selection

[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/

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

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.

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

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...