using CAST and LIKE
Hi all,
Sorry if this has already been posted but the archives, as you probably
already know, aren't searchable right now and I did not see anything
relevant when I sifted through them. Anywho, I'm trying to use a type
cast in a where ... like select statement and I've done it like so:
SELECT * FROM table WHERE CAST('field' AS TEXT) LIKE '%value%';
now the field I'm casting, be it already of type text or not, never
returns any rows. However if I use a field that is already of text type
and do not cast it then I get resluts. So my question is is there any
way to use CAST and LIKE together in the same select or am I
missing/doing something wrong? Thanks.
Timothy,
It is easier to use regular expressions
I am quite sure it works with string fields and integer fields
SELECT * FROM table WHERE field ~* 'value';
~* case insensitive
----- Original Message -----
From: "Timothy Wood" <timothy@hallcomp.com>
To: <pgsql-general@postgresql.org>
Sent: Monday, April 15, 2002 9:08 PM
Subject: [GENERAL] using CAST and LIKE
Show quoted text
Hi all,
Sorry if this has already been posted but the archives, as you probably
already know, aren't searchable right now and I did not see anything
relevant when I sifted through them. Anywho, I'm trying to use a type
cast in a where ... like select statement and I've done it like so:SELECT * FROM table WHERE CAST('field' AS TEXT) LIKE '%value%';
now the field I'm casting, be it already of type text or not, never
returns any rows. However if I use a field that is already of text type
and do not cast it then I get resluts. So my question is is there any
way to use CAST and LIKE together in the same select or am I
missing/doing something wrong? Thanks.Timothy,
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
Timothy Wood <timothy@hallcomp.com> writes:
SELECT * FROM table WHERE CAST('field' AS TEXT) LIKE '%value%';
I think you meant
SELECT * FROM table WHERE CAST(field AS TEXT) LIKE '%value%';
regards, tom lane
On Mon, 2002-04-15 at 15:46, Tom Lane wrote:
Timothy Wood <timothy@hallcomp.com> writes:
SELECT * FROM table WHERE CAST('field' AS TEXT) LIKE '%value%';
I think you meant
SELECT * FROM table WHERE CAST(field AS TEXT) LIKE '%value%';
regards, tom lane
Ahh, that does seem to work. Big difference without the single quotes.
What exactly is the big difference there or what do the single quotes do
that I was not aware of when I used them?
The regular expressions do seem like a better idea, but is there any
benefit over one or the other aside from the flexibility of the regexps?
Thanks again
Timothy,
En 15 Apr 2002 17:34:48 -0400
Timothy Wood <timothy@hallcomp.com> escribi�:
On Mon, 2002-04-15 at 15:46, Tom Lane wrote:
Timothy Wood <timothy@hallcomp.com> writes:
SELECT * FROM table WHERE CAST('field' AS TEXT) LIKE '%value%';
I think you meant
SELECT * FROM table WHERE CAST(field AS TEXT) LIKE '%value%';
Ahh, that does seem to work. Big difference without the single quotes.
What exactly is the big difference there or what do the single quotes do
that I was not aware of when I used them?
The difference is that field without quotes is an identifier (eg. a
field name), while a field with single quotes is a literal string. In
your query, Postgres was trying to match 'field' to '%value%' while it
obviously can't.
Note that it's different when you use double quotes. There, they are
used to mean that the identifier should not be case-transformed (sorry,
wrong verb); that way, you can query fields with names like "Field".
The regular expressions do seem like a better idea, but is there any
benefit over one or the other aside from the flexibility of the regexps?
Regexes cannot use indexes unless they are anchored at the beggining
(LIKE searches cannot either, but you have to unanchor them explicitly,
which is the opposite as you do with regexes). I dunno if the operations
are optimally implemented; if so, LIKE searches should be way faster as
they are much simpler in the general case (but should be equally fast as
simple regexes)
--
Alvaro Herrera (<alvherre[a]atentus.com>)
"Pensar que el espectro que vemos es ilusorio no lo despoja de espanto,
s�lo le suma el nuevo terror de la locura" (Perelandra, CSLewis)