Обсуждение: Re: Extract only numbers from a varchar column

I need to extract only the first occurence of numbers from a column  that is varchar.

Here are a few examples of what I need to do:

abc200xx -> 200
340ppsdd -> 340
150ytyty555 -> 150

Can this be done by a simple query or do I need to use a cursor?

Thanks

I've finally solved this way:

select name, regexp_replace(name, '\\D*', '', 'g')::int as replaced
from sometable
where regexp_replace(name, '\\D*', '', 'g') != ''

On Tuesday 06 May 2008 07:09:56 Leandro Casadei wrote:
> I need to extract only the first occurence of numbers from a column  that
> is varchar.
<snip>
> abc200xx -> 200
> 340ppsdd -> 340
> 150ytyty555 -> 150

tiger=> select substring('abc200xx300ff','[0-9]+');
 substring
-----------
 200
(1 row)

Time: 0.495 ms
tiger=> select substring('340ppsdd','[0-9]+');
 substring
-----------
 340
(1 row)

Time: 0.480 ms
tiger=> select substring('150ytyty555','[0-9]+');
 substring
-----------
 150
(1 row)

Time: 0.494 ms

tested on 8.2.  i don't have 8.3 here, so i don't know if there will
be type coercion issues there.  doubt it though.

tiger

--
Gerald Timothy Quimpo   bopolissimus@gmail.com
  If you don't know who Knuth is, then you're not a programmer. If
   you're a programmer and you don't know who Knuth is, well... you
   should probably be fired.
-- scienceblogs.com/goodmath/2008/01/the_genius_of_donald_knuth_typ.php