varchar => int

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/

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