Hi,

It looks like the routine I used to load the table put a null terminator
character in position 6. I re-wrote the routine to put in two extra spaces
like:

'99502 '

I also changed the column type to varchar(7) instead of char(7)

now I can do the querys.

But I have a question about whether to go back to char(7) vs varchar(7).
There are 800,000 rows (lots of canadian postal codes), so would I be
better off with one or the other? There are about 50,000 5-char codes,
and 750,000 7-char codes in the column.

Thanks,

Kyle

On 31 May 2001, at 12:54, Ivan Prenosil wrote:

> > I set up a column in a zip code table as char(7) so I could store both 7-
> > char Canadian and 5-char US postal codes.
> >
> > But now I cannot query the 5char zip codes. For example
> >
> > select * from MyTable where ZipCode='99502'
> >
> > produces no result. Yet the row is clearly in the table.
> >
> > I can do this:
> > select * from MyTable where ZipCode>'99501' and ZipCode<'99503'
> > and I will get the row
> >
> > What is the 7 char field padded with, and how can I do an equality query
> > against these zipcodes? I have tried with simple space pads ('99502 '),
> > and that doesn't work.
>
> The character used for padding depends on character set.
> All character sets IB uses are padded with space,
> except character set OCTETS, that is padded with binary zero.
>
> OCTETS is used for storing binary data, so it is unnecessary for postal codes.
>
> Just in case you really _need_ binary data in postal codes :-),
> you have to cast the string to the same character set, i.e.
>
> select * from MyTable where ZipCode=cast('99502' as char(7) character set octets)
>
>
> Ivan
> prenosil@...
> http://www.volny.cz/iprenosil/interbase
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>



http://pearz.com - A new whole-person approach to online dating