Обсуждение: need a query
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
What should be the sql?
Any help.
2010/3/2 AI Rumman <rummandba@gmail.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.