Re: Conversion of string to int using digits at beginning

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