Обсуждение: How to build a query
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
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
On 2 March 2010 10:06, Krzysztof Walkiewicz <bars0@op.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
W dniu 2010-03-02 11:21, Thom Brown pisze:
> On 2 March 2010 10:06, Krzysztof Walkiewicz<bars0@op.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