At 10:38 PM 20-11-01 -0500, you wrote:

>Hi:
>
> Have mailing database. Need to index (unique) in first three digits
>of Zip code, read that into a stringlist then iterate through the list
>calculating count for each unique set of 3 digits.
>
> How to create index on a sub string of a field?
>
> ZIP VARCHAR(15)
>
> I tried:
>
>CREATE UNIQUE ASC INDEX Zip3 ON NEWSLET SUBSTR(ZIP,1,3);
>
>Doesn't like the ( after SUBSTR. I use SUBSTR(ZIP,1,5) in other things
>and it works. What is problem with index?
>
>Zip field example: 48105-4456

FB and IB don't support indexing on expressions.

Create a proxy search column of the required size which is populated automatically by a trigger which writes the substring into it, and index that.

regards,
Helen


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