|Subject||Re: [ib-support] padding in char field???|
> I set up a column in a zip code table as char(7) so I could store both 7-The character used for padding depends on character set.
> 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.
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)