Thread: Returning String as Integer
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
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