Thread: Conversion of string to int using digits at beginning

Table contains CHAR(20) type columns containing numbers and other values.

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;

but got

ERROR:  invalid input syntax for integer: "23/3      "

How to fix this so that this query returns 23 without error ?

Andrus.

On 18/11/2008 17:33, Andrus wrote:

> 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;
>
> but got
>
> ERROR:  invalid input syntax for integer: "23/3      "
>
> How to fix this so that this query returns 23 without error ?

It's not a question of "fixing" - it's not broken! :-)

You could use a regular expression in substring() to get just the
numeric bits.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

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

On Tue, Nov 18, 2008 at 05:51:08PM +0000, Raymond O'Donnell wrote:
> You could use a regular expression in substring() to get just the
> numeric bits.

Thanks, never noticed that substring would accept a regexp before.
Syntax is a bit baroque, but it seems to work!


  Sam

Raymond,

> You could use a regular expression in substring() to get just the
> numeric bits.

Thank you.
I do'nt have any experience on regex.
Can you provide a sample how to use regex to get numeric substring from
start of string ?

Andrus.

Sam,

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

Thank you.
This seems to work but is bit slow.

How to speed it up ?

Should I create index

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

?

Andrus.

On Wed, Nov 19, 2008 at 12:50:23PM +0200, Andrus wrote:
> Sam,
> > SELECT MAX(nullif(regexp_replace(test, '^([0-9]*).*$', E'\\1'),'')::INT);
>
> Thank you.
> This seems to work but is bit slow.

It will have to be executed against every row before you get an answer,
so if you're just after the max of a whole table will be pretty slow.

> How to speed it up ?
> Should I create index
>
> CREATE INDEX test ON  test ( nullif(regexp_replace(test, '^([0-9]*).*$',
> E'\\1'),'')::INT );

that would work.  I'd be tempted to use the substring() function instead
as it looks a bit prettier (peformance seems indistuinguishable).  So
I'd use the following pair:

  CREATE INDEX test_test_idx ON test
    ((nullif(substring(test, '^[0-9]*'),'')::int));

and

  SELECT MIN(nullif(substring(test, '^[0-9]*'),'')::int) FROM test;

you could use a view as well, at which point you wouldn't have to
remember how you were converting the string into an int:

  CREATE VIEW test_v AS
    SELECT *, nullif(substring(test, '^[0-9]*'),'')::int AS test_int
    FROM test;

allowing a simple:

  SELECT MIN(test_int) FROM test_v;

hope that helps!


  Sam