Re: How to build a query

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