Обсуждение: Returning String as Integer

Em Sexta 05 Maio 2006 18:37, Kashmira Patel (kupatel) escreveu:
> Hi all,
>    I have a table with a column of type 'text'. It mainly contains
> numbers. Is there any way to select a value from this column and return
> it as an integer?

testing=# select '123'::integer;int4 
------ 123
(1 registro)

testing=# 


-- 
Jorge Godoy      <jgodoy@gmail.com>

Use the to_number() function to convert text to numbers.
In the manual under functions and operators.  The other
function like it is to_date().

--elein
elein@varlena.com

On Fri, May 05, 2006 at 02:37:13PM -0700, Kashmira Patel (kupatel) wrote:
> Hi all,
>    I have a table with a column of type 'text'. It mainly contains numbers. Is
> there any way to select a value from this column and return it as an integer?
>  
> Thanks,
> Kashmira

Here is a Perl function a friend of mine wrote to help with this...

Problems I ran into were errors because of NON NUMERIC Characters in the
String...

Casting would fail

It doesn't catch all cases but it has been quite helpful to me


$x = $_[0];
$x =~ s/^[^-\d\.]*//;
$x =~ s/\,//g;
$x = $x * 1;
return $x;


-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Jorge Godoy
Sent: Friday, May 05, 2006 4:52 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Returning String as Integer

Em Sexta 05 Maio 2006 18:37, Kashmira Patel (kupatel) escreveu:
> Hi all,
>    I have a table with a column of type 'text'. It mainly contains
> numbers. Is there any way to select a value from this column and
return
> it as an integer?

testing=# select '123'::integer;int4
------ 123
(1 registro)

testing=#


--
Jorge Godoy      <jgodoy@gmail.com>


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Thanks everyone :)

-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Jorge Godoy
Sent: Friday, May 05, 2006 2:52 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Returning String as Integer

Em Sexta 05 Maio 2006 18:37, Kashmira Patel (kupatel) escreveu:
> Hi all,
>    I have a table with a column of type 'text'. It mainly contains
> numbers. Is there any way to select a value from this column and
> return it as an integer?

testing=# select '123'::integer;int4
------ 123
(1 registro)

testing=#


--
Jorge Godoy      <jgodoy@gmail.com>


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Fri, May 05, 2006 at 18:52:19 -0300, Jorge Godoy <jgodoy@gmail.com> wrote:
> Em Sexta 05 Maio 2006 18:37, Kashmira Patel (kupatel) escreveu:
> > Hi all,
> >    I have a table with a column of type 'text'. It mainly contains
> > numbers. Is there any way to select a value from this column and return
> > it as an integer?
> 
> testing=# select '123'::integer;
>  int4 
> ------
>   123
> (1 registro)

Note that that isn't the same thing. The '123' above is not treated as
being of type text. If you actually wanted to test this conversion you
would want to do:
bruno=> select '123'::text::integer;int4
------ 123
(1 row)

Jorge Godoy <jgodoy@gmail.com> wrote:
> numbers. Is there any way to select a value from this column and return
> it as an integer?

My twopence. I just happened to have the same problem with iReports for a 
10 digit number - it may be case specific but in my case
    # select ... int4(id) ...;

worked.

I tried int8(id) but java didn't take it as Integer. (It took int8 as 
something like Long.)


Regards,

Ben

Em Sábado 06 Maio 2006 17:05, Ben K. escreveu:
> Jorge Godoy <jgodoy@gmail.com> wrote:
> > numbers. Is there any way to select a value from this column and return
> > it as an integer?
>
> My twopence. I just happened to have the same problem with iReports for a
> 10 digit number - it may be case specific but in my case

Please, take care with your quote attributions.  I've never asked such a
question here and, in fact, I was answering it.  The person who asked such a
question was Kashmira Patel.


Thanks,
--
Jorge Godoy      <jgodoy@gmail.com>

>> My twopence. I just happened to have the same problem with iReports for a
>> 10 digit number - it may be case specific but in my case
>
> Please, take care with your quote attributions.  I've never asked such a
> question here and, in fact, I was answering it.  The person who asked such a
> question was Kashmira Patel.

Sorry, I apologize.

Regards,

Ben K.

Hi, Ben,

Ben K. schrieb:
> I tried int8(id) but java didn't take it as Integer. (It took int8 as
> something like Long.)

Yes, and that's good, as PostgreSQL int8 and java long actually are the
same datatype (64-bit signed two's-complement).

PostgreSQL int4 and Java int are the same (32-bit), as well as int2 and
short.

HTH,
Markus