|Subject||RE: Case insensitive search on a memo field|
|Author||Svein Erling Tysvær|
>I’m using Firebird 2.5.3, and I am looking for text in a memo field, eg.
I take it you mean BLOB or VARCHAR, I’ve never heard of memo fields in Firebird?
>Select * from mytable t where t.mymemo like ‘%find me%’
>The will only find
>FIND ME or
>Find me or
>Find Me etc
>I’m tempted to do something like
>Select * from mytable t where lowercase(t.mymemo) like ‘%find me%’
>but since this memo field could be enormous, I’m guessing that wold be horribly inefficient.
>Is there a better way?
I think SELECT * FROM MYTABLE WHERE MYMEMO CONTAINING ‘FIND ME’ is case insensitive. One, probably better, alternative would be to use a case insensitive collation, but I doubt collations can be used in blobs (it should work in a varchar). And you’re right, it will be slow since no indexes can be used (hopefully, we’re not talking about more than a few million rows in this table?)…