pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedReturning String as Integer
10+ messages / 7 participants
[nested] [flat]
* Returning String as Integer
@ 2006-05-05 21:37 Kashmira Patel (kupatel) <kupatel@cisco.com>
0 siblings, 2 replies; 10+ messages in thread
From: Kashmira Patel \(kupatel\) @ 2006-05-05 21:37 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Returning String as Integer
@ 2006-05-05 21:51 elein <elein@varlena.com>
parent: Kashmira Patel (kupatel) <kupatel@cisco.com>
1 sibling, 0 replies; 10+ messages in thread
From: elein @ 2006-05-05 21:51 UTC (permalink / raw)
To: Kashmira Patel (kupatel) <kupatel@cisco.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Returning String as Integer
@ 2006-05-05 21:52 Jorge Godoy <jgodoy@gmail.com>
parent: Kashmira Patel (kupatel) <kupatel@cisco.com>
1 sibling, 1 reply; 10+ messages in thread
From: Jorge Godoy @ 2006-05-05 21:52 UTC (permalink / raw)
To: pgsql-sql
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>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Returning String as Integer
@ 2006-05-05 21:58 Hogan, James F. Jr. <JHogan@seton.org>
0 siblings, 0 replies; 10+ messages in thread
From: Hogan, James F. Jr. @ 2006-05-05 21:58 UTC (permalink / raw)
To: Jorge Godoy <jgodoy@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Returning String as Integer
@ 2006-05-05 22:04 Kashmira Patel (kupatel) <kupatel@cisco.com>
0 siblings, 0 replies; 10+ messages in thread
From: Kashmira Patel (kupatel) @ 2006-05-05 22:04 UTC (permalink / raw)
To: Jorge Godoy <jgodoy@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Returning String as Integer
@ 2006-05-06 15:39 Bruno Wolff III <bruno@wolff.to>
parent: Jorge Godoy <jgodoy@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Bruno Wolff III @ 2006-05-06 15:39 UTC (permalink / raw)
To: Jorge Godoy <jgodoy@gmail.com>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Returning String as Integer
@ 2006-05-06 20:05 Ben K. <bkim@coe.tamu.edu>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 10+ messages in thread
From: Ben K. @ 2006-05-06 20:05 UTC (permalink / raw)
To: pgsql-sql; +Cc: Jorge Godoy <jgodoy@gmail.com>
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Returning String as Integer
@ 2006-05-06 21:38 Jorge Godoy <jgodoy@gmail.com>
parent: Ben K. <bkim@coe.tamu.edu>
1 sibling, 1 reply; 10+ messages in thread
From: Jorge Godoy @ 2006-05-06 21:38 UTC (permalink / raw)
To: pgsql-sql
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>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Returning String as Integer
@ 2006-05-07 02:35 Ben K. <bkim@coe.tamu.edu>
parent: Jorge Godoy <jgodoy@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Ben K. @ 2006-05-07 02:35 UTC (permalink / raw)
To: Jorge Godoy <jgodoy@gmail.com>; +Cc: pgsql-sql
>> 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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Returning String as Integer
@ 2006-05-07 08:39 Markus Schaber <schabi@logix-tt.com>
parent: Ben K. <bkim@coe.tamu.edu>
1 sibling, 0 replies; 10+ messages in thread
From: Markus Schaber @ 2006-05-07 08:39 UTC (permalink / raw)
To: Ben K. <bkim@coe.tamu.edu>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2006-05-07 08:39 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-05 21:37 Returning String as Integer Kashmira Patel (kupatel) <kupatel@cisco.com>
2006-05-05 21:51 ` elein <elein@varlena.com>
2006-05-05 21:52 ` Jorge Godoy <jgodoy@gmail.com>
2006-05-06 15:39 ` Bruno Wolff III <bruno@wolff.to>
2006-05-06 20:05 ` Ben K. <bkim@coe.tamu.edu>
2006-05-06 21:38 ` Jorge Godoy <jgodoy@gmail.com>
2006-05-07 02:35 ` Ben K. <bkim@coe.tamu.edu>
2006-05-07 08:39 ` Markus Schaber <schabi@logix-tt.com>
2006-05-05 21:58 Re: Returning String as Integer Hogan, James F. Jr. <JHogan@seton.org>
2006-05-05 22:04 Re: Returning String as Integer Kashmira Patel (kupatel) <kupatel@cisco.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox