Re: Extract only numbers from a varchar column - Mailing list pgsql-general

From Leandro Casadei
Subject Re: Extract only numbers from a varchar column
Date
Msg-id 5983afbb0805051209i5f358552wca696ef508fbac83@mail.gmail.com
Whole thread Raw
Responses Re: Extract only numbers from a varchar column  (Gerald Quimpo <bopolissimus.lists@gmail.com>)
List pgsql-general
---------- 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') != ''

pgsql-general by date:

Previous
From: "Robert Max Kramer"
Date:
Subject: PostgreSQL 8.3.x Win32-Releases - always without psqlODBC?
Next
From: "Leandro Casadei"
Date:
Subject: Extract only numbers from a varchar column