Thread: 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

http://www.pgexplorer.com


----- 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


> 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?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

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)