Re: Extract only numbers from a varchar column

------=_Part_20723_11685156.1210014596357
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

---------- Forwarded message ----------
From: Leandro Casadei <mateamargo@[EMAIL PROTECTED]
>
Date: May 5, 2008 2:08 PM
Subject: Extract only numbers from a varchar column
To: pgsql-general@[EMAIL PROTECTED]
 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') != ''

------=_Part_20723_11685156.1210014596357
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

---------- Forwarded message ----------<br><span class="gmail_quote">From:
<b class="gmail_sendername">Leandro Casadei</b> &lt;<a
href="mailto:mateamargo@[EMAIL PROTECTED]
">mateamargo@[EMAIL PROTECTED]
>&gt;<br>Date:
May 5, 2008 2:08 PM<br>
Subject: Extract only numbers from a varchar column<br>To: <a
href="mailto:pgsql-general@[EMAIL PROTECTED]
">pgsql-general@[EMAIL PROTECTED]
><br><br></span><p>I
need to extract only the first occurence of numbers from a column
&nbsp;that is varchar.</p>
<p>Here are a few examples of what I need to do:</p><p>abc200xx -&gt;
200<br>340ppsdd -&gt; 340<br>150ytyty555 -&gt; 150<br></p><p>
Can this be done by a simple query or do I need to use a
cursor?</p><p>Thanks</p>
<br clear="all"><p></p><p>I<span>&#39;ve finally solved this
way:</span></p><p><span>select 	name, regexp_replace(name, &#39;\\D*&#39;,
&#39;&#39;, &#39;g&#39;)::int as replaced<br>from
	sometable<br>where	regexp_replace(name, &#39;\\D*&#39;, &#39;&#39;,
&#39;g&#39;) != &#39;&#39;<br>
<br></span></p>

------=_Part_20723_11685156.1210014596357--