LIKE operator

[Charset iso-8859-1 unsupported, filtering to ASCII...]

Hi,

With mSQL I used to perform queries like below from within PHP:

select field1, field2 from table1 where field1 like '%$some_variable%'

where the '%' in the LIKE clause matches 0 or more characters of any value.
That is, the query above would match the strings that contain
'$some_variable' OR any string if $some_variable is empty.

For example: my table has two rows with the first record containing
field1='abc' and the second record containing field1='cde'.

If '$some_variable' is empty, then my query would return both rows.
If $some_variable='bc', then the query would return only the row where
field1='abc'.

With PostgreSQL I experience the following:

If '$some_variable' is empty, then my query returns *nothing*.
If $some_variable='bc', then the query would return only the row where
field1='abc'.

As the value of '$some_variable' is to be filled by some user while posting
a form, I don't know in advance if it is going to be empty or not.

How can I make the query results to be exactly as with mSQL without having
to alter my query 'throwing out' every part of the 'where' clause where
'$some_variable' is empty?

With 6.5 beta, I get:

test=> create table t (x text);
CREATE
test=> insert into t values ('abc');
INSERT 18697 1
test=> insert into t values ('bcd');
INSERT 18698 1
test=> select * from t where x like '%%';
x
---
abc
bcd
(2 rows)

Not sure what 6.4 does, but I will say I overhauled the LIKE code in
6.5beta.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

If it really has to do with the version, then I cannot do anything about
it...

My ISP runs version 6.3, and they will always be a little bit not
up-to-date.

Thanks a lot,

Paulo
pparola@brazilinfo.com

-----Mensagem original-----
De: Bruce Momjian <maillist@candle.pha.pa.us>
Para: PostgreSQL <pgsql@brazilinfo.com>
Cc: pgsql-general@postgreSQL.org <pgsql-general@postgreSQL.org>
Data: Sexta-feira, 23 de Abril de 1999 17:56
Assunto: Re: [GENERAL] LIKE operator

[Charset iso-8859-1 unsupported, filtering to ASCII...]

Hi,

With mSQL I used to perform queries like below from within PHP:

select field1, field2 from table1 where field1 like '%$some_variable%'

where the '%' in the LIKE clause matches 0 or more characters of any

value.

That is, the query above would match the strings that contain
'$some_variable' OR any string if $some_variable is empty.

For example: my table has two rows with the first record containing
field1='abc' and the second record containing field1='cde'.

If '$some_variable' is empty, then my query would return both rows.
If $some_variable='bc', then the query would return only the row where
field1='abc'.

With PostgreSQL I experience the following:

If '$some_variable' is empty, then my query returns *nothing*.
If $some_variable='bc', then the query would return only the row where
field1='abc'.

As the value of '$some_variable' is to be filled by some user while

posting

a form, I don't know in advance if it is going to be empty or not.

How can I make the query results to be exactly as with mSQL without

having