At 11:30 AM 31-05-01 -0700, you wrote:

>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.

Varchar is the correct type to use where your character field size can vary. There is no performance cost to this.

The "cost", if any, is in the validation you need to do client-side to handle two different length prerequisites for the same database column. If the length is going to be important once the data gets over to the server (e.g. you want the server to perform a check via a trigger) you might like to consider adding a flag column to the table to mark whether the postcode is CA or US, so that the trigger knows what it is getting.

Regards,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________