Skip site navigation (1) Skip section navigation (2)

Re: need a query

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: AI Rumman <rummandba(at)gmail(dot)com>
Cc: pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: Re: need a query
Date: 2010-03-02 12:35:00
Message-ID: 162867791003020435j61d4386ejbdc81925e4841a99@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
2010/3/2 AI Rumman <rummandba(at)gmail(dot)com>:
> I have data as followos:
>
> Table: contact
> column: phone as varchar
>  +1 (800) 863-3950 ext. 517
> +1.510.291.6100 ext2347
>  +1.714.545.8886 ext 144
> 714.545.8887 ext 144
> 714.545.8898
> +1.510.291.6101
>
> I need to extract only the phone numbers using a SQL query like:
>
>  +1 (800) 863-3950 ext. 517 ------- 8008603950
> +1.510.291.6100 ext2347  --------  5102916100
>  +1.714.545.8886 ext 144 ---------  7145458886
> 714.545.8887 ext 144  ----------- 7145458887
> 714.545.8898 -------------- 7145458898
> +1.510.291.6101 ------------- 5102916101
>

look regular expression

postgres=# select (regexp_matches(regexp_replace('+1 (800) 863-3950
ext. 517',e'(\\.)|(\\+1)|\\(|\\)| |-','','g'), e'\\d+'))[1];
 regexp_matches
----------------
 8008633950
(1 row)

Regards
Pavel Stehule

> What should be the sql?
>
> Any help.

In response to

pgsql-general by date

Next:From: Hiroshi InoueDate: 2010-03-02 14:40:46
Subject: Re: [GENERAL] trouble with to_char('L')
Previous:From: Craig RingerDate: 2010-03-02 12:12:02
Subject: Re: How to grant a user read-only access to a database?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group