Re: How to build a query

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