Thread: varchar => int

varchar => int

From
"Mitch Vincent"
Date:
Well, here I am trying to do a range search on a varchar field (though it
has numbers in it...)..

I can't cast varchar to int (according to the error I'm getting..), any
ideas on how I can accomplish a ranged search on a varchar field (taking
into acount the value of the numbers, not the character's ascii code)?

I know. It's a messed up thing to do.. I didn't design these tables, I think
it's like that so the zip codes (what's stored in this table) could contain
dashes... Now they want to search it with a range.... *sigh*..

Thanks for any ideas..

-Mitch


Re: varchar => int

From
Peter Eisentraut
Date:
Mitch Vincent writes:

> I can't cast varchar to int (according to the error I'm getting..), any
> ideas on how I can accomplish a ranged search on a varchar field (taking
> into acount the value of the numbers, not the character's ascii code)?

Try casting it to text first:

cast(cast(value as text) as integer)

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/


Re: varchar => int

From
"Mitch Vincent"
Date:
Yes, Peter's suggestion does work and I was using the substring already..
This guy has some that have Xs in them too (lord knows why), so it's either
function writing time as you suggest or time to beat a client :-)

Thanks for the suggestions guys, I appreciate it..

-Mitch


----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Mitch Vincent" <mitch@venux.net>
Cc: <pgsql-general@postgresql.org>
Sent: Sunday, January 28, 2001 1:39 PM
Subject: Re: [GENERAL] varchar => int


>
> Well, you can get there via text (as Peter said) except that I think
> that if any don't convert (ie nnnnn-nnnn) it's going to error since
> at least on current source it appears to make sure that the entire
> string is numeric.
>
> I guess if you knew that the first five were *always* going to be numeric
> you could use substring.  I think your best bet is to write your
> own function though.
>
> On Sat, 27 Jan 2001, Mitch Vincent wrote:
>
> > Well, here I am trying to do a range search on a varchar field (though
it
> > has numbers in it...)..
> >
> > I can't cast varchar to int (according to the error I'm getting..), any
> > ideas on how I can accomplish a ranged search on a varchar field (taking
> > into acount the value of the numbers, not the character's ascii code)?
> >
> > I know. It's a messed up thing to do.. I didn't design these tables, I
think
> > it's like that so the zip codes (what's stored in this table) could
contain
> > dashes... Now they want to search it with a range.... *sigh*..
> >
> > Thanks for any ideas..
> >
> > -Mitch
> >
>
>