Extract only numbers from a varchar column

---------- Forwarded message ----------
From: Leandro Casadei <mateamargo@gmail.com>
Date: May 5, 2008 2:08 PM
Subject: Extract only numbers from a varchar column
To: pgsql-general@postgresql.org

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