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

Re: How to build a query

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: How to build a query
Date: 2010-03-02 10:21:35
Message-ID: 20100302102135.GA25194@a-kretschmer.de (view raw or flat)
Thread:
Lists: pgsql-novice
In response to Krzysztof Walkiewicz :
> Hello everyone!
> 
> In my table I have a column phone_numbers (varchar 30) that the 
> telephone numbers are stored in the following way (13 signs with spaces):
> 
> 032 321 24 25
> 032 341 24 85
> 032 345 24 87
> 
> But in the same column there is also few hundred of mobile numbers that 
> i want to copy to another table. They were written in the following way 
> (11 signs with spaces):
> 
> 606 605 504
> 506 504 548
> 879 504 152
> 
> Now I have a question: how to built a query that will give me only the 
> rows that are shorter than 13 signs (and maybe longer than 13 to check 
> if there is no mistakes)?

You can use regular expressions, for instance:

test=*# select * from phone ;
       t
---------------
 032 321 24 25
 606 605 504
(2 rows)

test=*# select * from phone where t ~ E'^[0-9]{3} [0-9]{3} [0-9]{2} [0-9]{2}$';
       t
---------------
 032 321 24 25
(1 row)

test=*# select * from phone where t ~ E'^[0-9]{3} [0-9]{3} [0-9]{3}$';
      t
-------------
 606 605 504
(1 row)



Regards, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

In response to

pgsql-novice by date

Next:From: Krzysztof WalkiewiczDate: 2010-03-02 11:13:03
Subject: Re: How to build a query
Previous:From: Thom BrownDate: 2010-03-02 10:21:34
Subject: Re: How to build a query

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