pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedconverting varchar to integer
7+ messages / 6 participants
[nested] [flat]
* converting varchar to integer
@ 2005-08-16 23:34 tv@fuzzy.cz
0 siblings, 3 replies; 7+ messages in thread
From: tv@fuzzy.cz @ 2005-08-16 23:34 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have a varchar column, and I need to
1) check the value in it is an integer
2) get the integer value (as integer)
The problem is I can't suppose the're only correct
values - ie there can be something like 'xssdkjsd',
'230kdd' or even an empty string etc.
I've been looking through the documentation but I've
found no functions doing this. Are there such functions?
I've been using to_number(...) function, but it raises
an exception on an empty string.
I've written two on my own (see the functions below),
but maybe there's something faster?
Tomas
---------------------------------------------------------------------
-- converts the varchar value to integer
-- the value has to be already checked using the is_integer function
---------------------------------------------------------------------
CREATE OR REPLACE FUNCTION to_integer(VARCHAR) RETURNS INTEGER AS '
DECLARE
str ALIAS FOR $1;
BEGIN
RETURN to_number(str,9999999999);
END;
' LANGUAGE plpgsql;
---------------------------------------------------------------------
-- checks whether the value is an integer (int4)
---------------------------------------------------------------------
CREATE OR REPLACE FUNCTION is_integer(VARCHAR) RETURNS BOOLEAN AS '
DECLARE
str ALIAS FOR $1;
pos INT8 := 0;
BEGIN
-- only 0,1,...,9 (least one)
IF NOT str ~* ''^[0-9]+$'' THEN
RETURN false;
END IF;
SELECT INTO pos to_number($1,9999999999);
-- check the boundaries
IF (-2147483648 <= pos) AND (+2147483647 >= pos) THEN
RETURN true;
ELSE
RETURN false;
END IF;
END;
' LANGUAGE plpgsql;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: converting varchar to integer
@ 2005-08-17 00:05 Josh Berkus <josh@agliodbs.com>
parent: tv@fuzzy.cz
2 siblings, 0 replies; 7+ messages in thread
From: Josh Berkus @ 2005-08-17 00:05 UTC (permalink / raw)
To: pgsql-sql; +Cc: tv@fuzzy.cz
Tomas,
> I've written two on my own (see the functions below),
> but maybe there's something faster?
Nope. 'cept I'd combine those two functions into a single function that
returns NULL if the value isn't an integer.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [despammed] converting varchar to integer
@ 2005-08-17 09:30 Kretschmer Andreas <andreas_kretschmer@despammed.com>
parent: tv@fuzzy.cz
2 siblings, 0 replies; 7+ messages in thread
From: Kretschmer Andreas @ 2005-08-17 09:30 UTC (permalink / raw)
To: pgsql-sql
tv@fuzzy.cz <tv@fuzzy.cz> schrieb:
> Hi,
>
> I have a varchar column, and I need to
>
> 1) check the value in it is an integer
> 2) get the integer value (as integer)
>
> The problem is I can't suppose the're only correct
> values - ie there can be something like 'xssdkjsd',
> '230kdd' or even an empty string etc.
test=# select * from foo;
t | n
------+---
bla |
bla1 |
2 |
(3 rows)
test=# update foo set n = substring(t , '[0-9]')::int;
UPDATE 3
test=# select * from foo;
t | n
------+---
bla |
bla1 | 1
2 | 2
(3 rows)
Regards, Andreas
--
Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau-
fenden Pinguins aus artgerechter Freilandhaltung. Er ist garantiert frei
von Micro$oft'schen Viren. (#97922 http://counter.li.org) GPG 7F4584DA
Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: converting varchar to integer
@ 2005-08-17 10:46 Halley Pacheco de Oliveira <halleypo@yahoo.com.br>
0 siblings, 0 replies; 7+ messages in thread
From: Halley Pacheco de Oliveira @ 2005-08-17 10:46 UTC (permalink / raw)
To: pgsql-sql; +Cc: tv@fuzzy.cz
Using CASE to avoid '':
CREATE TABLE test (number TEXT);
INSERT INTO test VALUES('123');
INSERT INTO test VALUES('a123b');
INSERT INTO test VALUES('');
teste=> SELECT CASE number WHEN '' THEN NULL ELSE to_number(number,'999999999999990') END AS
number FROM test;;
number
--------
123
123
(3 lines)
__________________________________________________
Converse com seus amigos em tempo real com o Yahoo! Messenger
http://br.download.yahoo.com/messenger/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [despammed] converting varchar to integer
@ 2005-08-17 16:46 Frank Bax <fbax@sympatico.ca>
parent: tv@fuzzy.cz
2 siblings, 1 reply; 7+ messages in thread
From: Frank Bax @ 2005-08-17 16:46 UTC (permalink / raw)
To: pgsql-sql
At 05:30 AM 8/17/05, Kretschmer Andreas wrote:
>tv@fuzzy.cz <tv@fuzzy.cz> schrieb:
>
> > Hi,
> >
> > I have a varchar column, and I need to
> >
> > 1) check the value in it is an integer
> > 2) get the integer value (as integer)
>
>test=# update foo set n = substring(t , '[0-9]')::int;
I think you meant:
update foo set n = substring(t , '[0-9]+')::int;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [despammed] converting varchar to integer
@ 2005-08-17 17:40 A. Kretschmer <akretschmer@despammed.com>
parent: Frank Bax <fbax@sympatico.ca>
0 siblings, 0 replies; 7+ messages in thread
From: A. Kretschmer @ 2005-08-17 17:40 UTC (permalink / raw)
To: pgsql-sql
am 17.08.2005, um 12:46:01 -0400 mailte Frank Bax folgendes:
> >> 1) check the value in it is an integer
> >> 2) get the integer value (as integer)
> >test=# update foo set n = substring(t , '[0-9]')::int;
>
>
> I think you meant:
> update foo set n = substring(t , '[0-9]+')::int;
Yes, of corse. Thank you.
Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [despammed] converting varchar to integer
@ 2005-08-18 14:25 Halley Pacheco de Oliveira <halleypo@yahoo.com.br>
0 siblings, 0 replies; 7+ messages in thread
From: Halley Pacheco de Oliveira @ 2005-08-18 14:25 UTC (permalink / raw)
To: pgsql-sql
A function to check for valid integers:
CREATE OR REPLACE FUNCTION retInt(VARCHAR) RETURNS integer AS '
DECLARE
number ALIAS FOR $1;
i INTEGER := 1;
BEGIN
IF ((number IS NULL) OR (number = '''')) THEN
RETURN NULL;
END IF;
WHILE (i <= length(number)) LOOP
IF ((substr(number,i,1) < ''0'') OR (substr(number,i,1)) > ''9'') THEN
RETURN NULL;
END IF;
i := i + 1;
END LOOP;
RETURN to_number(number,''999999999999990'');
END;
' LANGUAGE plpgsql;
\pset null (NULL)
DROP TABLE test;
CREATE TABLE test (number VARCHAR);
INSERT INTO test VALUES('123');
INSERT INTO test VALUES('a123');
INSERT INTO test VALUES('123b');
INSERT INTO test VALUES('');
SELECT retInt(number) FROM test;
retint
--------
123
(NULL)
(NULL)
(NULL)
(4 lines)
_______________________________________________________
Yahoo! Acesso Grátis - Internet rápida e grátis.
Instale o discador agora! http://br.acesso.yahoo.com/
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-08-18 14:25 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-16 23:34 converting varchar to integer tv@fuzzy.cz
2005-08-17 00:05 ` Josh Berkus <josh@agliodbs.com>
2005-08-17 09:30 ` Kretschmer Andreas <andreas_kretschmer@despammed.com>
2005-08-17 16:46 ` Frank Bax <fbax@sympatico.ca>
2005-08-17 17:40 ` A. Kretschmer <akretschmer@despammed.com>
2005-08-17 10:46 Re: converting varchar to integer Halley Pacheco de Oliveira <halleypo@yahoo.com.br>
2005-08-18 14:25 Re: [despammed] converting varchar to integer Halley Pacheco de Oliveira <halleypo@yahoo.com.br>
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