problems with leading zero's

On Wed, Nov 19, 2003 at 11:07:22 -0800,
Bryan Irvine 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.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html