Обсуждение: 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


>
> 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.
>
> ********** Error **********
>
> ERROR: operator does not exist: integer ~~ unknown
>
> SQL state: 42883
>
> Hint: No operator matches the given name and argument type(s). You might
> need to add explicit type casts.
>
> Character: 34"
>
> I don't know why because I did before more difficult querys and works well.
> What is the problem?
>
> Thanks a lot
>
> JP Cook

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