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

Re: How to build a query

From: Krzysztof Walkiewicz <bars0(at)op(dot)pl>
To: Thom Brown <thombrown(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: How to build a query
Date: 2010-03-02 11:13:03
Message-ID: 4B8CF2BF.4020607@op.pl (view raw or flat)
Thread:
Lists: pgsql-novice
W dniu 2010-03-02 11:21, Thom Brown pisze:
> On 2 March 2010 10:06, Krzysztof Walkiewicz<bars0(at)op(dot)pl>  wrote:
>> 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)?
>>
>> Regards,
>> Krzysztof Walkiewicz
>>
>
> Well you could do:
>
> SELECT phone_numbers FROM my_table WHERE length(phone_number)<  13;
>
> or, if you want to match a specific number pattern, try:
>
> SELECT phone_numbers FROM my_table WHERE phone_number ~ E'^\\d{3}
> \\d{3} \\d{2} \\d{2}$';
>
> That last one would match against your first set of phone numbers.
>
> You may also wish to put a constraint on your column, or change your
> datatype, to ensure the values being entered are correct in the first
> place.  If you use varchar(13), you can't enter values longer than 13
> characters.
>
> Regards
>
> Thom
>
Thank You for your help. It works!

Krzysztof

In response to

pgsql-novice by date

Next:From: Danny LoDate: 2010-03-02 21:39:42
Subject: Using EXIT and labels to exit blocks of statements
Previous:From: A. KretschmerDate: 2010-03-02 10:21:35
Subject: Re: How to build a query

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