Re: Conversion of string to int using digits at beginning

[Sam Mason <sam@xxxxxxxxxxxxx>]

On Tue, Nov 18, 2008 at 07:33:47PM +0200, Andrus wrote:
> I need to obtain max integer considering only numbers from start of column 
> up to first non-integer character.
> 
> I tried
> 
> create temp table test (test char(20));
> insert into test values ('12');
> insert into test values ('23/3');
> insert into test values ('AX/3');
> select max(test::int) from test;

Maybe something like:

  SELECT MAX(nullif(regexp_replace(test, '^([0-9]*).*$', E'\\1'),'')::INT);

The following parts of the manual may help:

  http://www.postgresql.org/docs/current/static/functions-string.html
  http://www.postgresql.org/docs/current/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP


  Sam

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general