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
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 );
?
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.
--
Sent via pgsql-general mailing list (pgsql-gener
On Tue, Nov 18, 2008 at 05:51:08PM +, 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
--
Sent via pgsql-gener
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');
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 "
>
> Ho
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/
