Обсуждение: problems with leading zero's

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

On Wed, Nov 19, 2003 at 11:07:22 -0800,
  Bryan Irvine <bryan.irvine@kingcountyjournal.com> wrote:
> I'm having problems with my SQL selecting numbers with preceding zero's
> from a character field.

You want to quote the digits so they get treated as a string instead of
a number which then gets converted to a string.

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