Re: problems with leading zero's

Hi,

Just try :

select * from phonenumbers where areacode = 865 and prefix = 379 and \
suffix = '0044';

note the quote (') before and after 0044.

You are using string and not number.


Bruno




Bryan Irvine wrote:

>I'm having problems with my SQL selecting numbers with preceding zero's
>from a character field.
>
>I'm creating the fields like this:
>CREATE TABLE phonenumbers (
>            areacode character varying(3),
>                prefix character varying(3),
>                    suffix character varying(4)
>                    );
>
>when I run a broad select based on areacode and prefix i get this
>(numbers munged to protect the innocent)
>select * from phonenumbers where areacode = 555 and prefix = 555;
>
> areacode | prefix | suffix
>----------+--------+--------
> 555      | 555    | 0044
> 555      | 555    | 0049
>...etc
>
>but when i try to do a more exact search for a number with a suffix that
>starts with a zero, nothing comes back.
>
>select * from phonenumbers where areacode = 865 and prefix = 379 and \
>suffix = 0044;
> areacode | prefix | suffix
>----------+--------+--------
>(0 rows)
>
>Ideas?
>
>--Bryan
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>
>

--
Bruno LEVEQUE
System Engineer
SARL NET6D
bruno.leveque@net6d.com
http://www.net6d.com