LIKE problem

2009/7/6 Juan Pablo Cook <juampick@gmail.com>:

Hi Everybody!
I'm asking you, because recently I have some problems with the LIKE so I
test with an easy and simple query but doesn't work:
SELECT *
FROM employee
WHERE id like 'h%';
The error says:
"ERROR:  operator does not exist: integer ~~ unknown

SELECT *
FROM employee
WHERE name like 'h%'; --<<<

there are not LIKE operator for integer values. you cannot use LIKE on
integer columns

regards
Pavel

In response to Juan Pablo Cook :

Hi Everybody!�
I'm asking you, because recently I have some problems with the LIKE so I test
with an easy and simple query but doesn't work:

SELECT *
FROM employee
WHERE id like 'h%';

The error says:
"ERROR:� operator does not exist: integer ~~ unknown

LINE 5: WHERE id like 'h%';

� � � � � � � � � ^

HINT:� No operator matches the given name and argument type(s). You might need
to add explicit type casts.

You can't compare a INT with a TEXT:

test=*# select '1'::int4 like '1'::text;
ERROR: operator does not exist: integer ~~ text
LINE 1: select '1'::int4 like '1'::text;
^
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.

But you can CAST the INT into a TEXT:

test=# select '1'::int4::text like '1%'::text;
?column?
----------
t
(1 row)

Okay?

But in your case,you have an INT-column (id) and you compare that with
'h%'. You can CAST the id-column, but all INT::TEXT don't match 'h%'.

--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net