Re: Extract only numbers from a varchar column

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(at)gmail(dot)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