Re: Question about like

Cornelia Boenigk wrote:

> Merry Christmas and hello everybody
>


And to you!

> I have a test table with a field called 'name' which have three
> entries:
> Johanna
> Karl
> Uwe
>
> If I query this table with
> - select name from test where name like 'Uwe';
> - select name from test where name like 'U_e';
> - select name from test where name like '%e';
> or
> - select name from test where name like '---';
>
> in all cases pg responds with 0 rows found.
>
> I was expecting another result;-(
> Does anybody have a hint or an explanation for this behaviour?
> My system is RedHat 7.1 with PostgreSQL 7.1.3
>

You haven't shown us how your "test" table is defined, but if "name" is
a CHAR() field, you need to allow for the spaces padded to the end of
'Ewe'. With TEXT or VARCHAR you don't need to worry about that. See the
examples below:


test=# create table test1(name text);
CREATE
test=# create table test2(name char(25));
CREATE
test=# create table test3(name varchar(25));
CREATE
test=# insert into test1 values('Uwe');
INSERT 1492521 1
test=# insert into test2 values('Uwe');
INSERT 1492522 1
test=# insert into test3 values('Uwe');
INSERT 1492523 1
test=# select name from test1 where name like 'Uwe';
  name
------
  Uwe
(1 row)

test=# select name from test1 where name like 'U_e';
  name
------
  Uwe
(1 row)

test=# select name from test1 where name like '%e';
  name
------
  Uwe
(1 row)

test=# select name from test2 where name like 'Uwe';
  name
------
(0 rows)

test=# select name from test2 where name like 'Uwe%';
            name
---------------------------
  Uwe
(1 row)

test=# select name from test2 where name like 'U_e';
  name
------
(0 rows)

test=# select name from test2 where name like 'U_e%';
            name
---------------------------
  Uwe
(1 row)

test=# select name from test2 where name like '%e';
  name
------
(0 rows)

test=# select name from test2 where name like '%e%';
            name
---------------------------
  Uwe
(1 row)

test=# select name from test3 where name like 'Uwe';
  name
------
  Uwe
(1 row)

test=# select name from test3 where name like 'U_e';
  name
------
  Uwe
(1 row)

test=# select name from test3 where name like '%e';
  name
------
  Uwe
(1 row)

test=# select version();
                            version
-------------------------------------------------------------
  PostgreSQL 7.2b3 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)


Hope this helps,

Joe