Wes wrote:

>OK, I found out that I cannot use a statement like
>
>Select Upper(description) from items
>
>or
>
>select
> description
>from
> items
>where
> upper(description) like 'COMPUTER%'
>
>when the field "description" is a BLOB field
>because it produces the error:
>
>Overflow occurred during data type conversion.
>Conversion error from string BLOB
>
>I guess this is how the engine works, or is this an error that I can
>get around. In my case, the field "description" is a bunch of
>historical notes about an inventory item. For my imediate solution,
>I will be using the "containing" key word in a select statement, but
>if I wanted to do a case insensitive select finding the matching
>characters starting from the first character, I guess I'm out of
>luck. I haven't looked into a UDF, but I will and it may help me
>out. Thanks for all the responses. This is truely a great active
>forum.
>
>Wes
>

Wes,

You could do something like this:

select
substring(description from 1 for 200)
from
Products
where
Upper(substring(description from 1 for 200)) like 'COMPUTER%'

bit it will be again slow, since no index could be used in this query.

What do you think about creating a proxy column that holds the 200 or so
first chars of the blob ?

You could populate this column in after insert/delete triggers and
create an index on it and then use this query:

select
*
from
Products
where
Proxy_Description like 'COMPUTER%'

this query will use an index and will be very fast !

the overhead caused by the triggers on insert/update will be insignificant.

see you !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.6.1 - Release Date: 03/06/2005