Обсуждение: converting varchar to integer

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
evenan 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
RETURNfalse;   END IF;
 
END;
' LANGUAGE plpgsql;

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

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 | 12    | 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° ;-)

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/ 

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;

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    ===