Is there a possibility of a simple LIKE which ignores the single character wildcard and doesn't use sliding windows?

I'd be happy with either a new verb or the current LIKE to handle %something% as a special case.

The reason for the request is that whilst LIKE handles small volumes of text (eg VARCHAR(100)) very well it takes an inordinate amount of time to scan CLOBs with a "reasonable" amount of data.

Before anyone suggests it full text indexing is not an option but neither is waiting over 20 minutes for LIKE to process c3.5k records.

I have written my own external function using Delphi to cope with it but I'd like to see it as part of the engine.

Roy Lambert

>Is there a possibility of a simple LIKE which ignores the single character wildcard and doesn't use sliding windows?<

Roy,

To eliminate the "_" as a wildcard, can you not use something like:

select * from table where colname like Replace('_','\_',SearchStr) escape '\';


Barry

Barry

>To eliminate the "_" as a wildcard, can you not use something like:
>
>select * from table where colname like Replace('_','\_',SearchStr) escape '\';

Its not eliminating something from what I use as a search term its how the searching is implemented.

Tim has provided a very flexible implementation of LIKE which allows patterns like '%fred%w_s%here%' to be searched for, in a way that honors the collation as well. Its very powerful but way beyond what I need. All I'm bothered about is '%fred' or 'fred%' or '%fred%'. Most of the time the current implementation is fast enough but with large CLOBS it runs like the proverbial dog with three legs (a strange metaphor when you think of some of the videos I've seen with three legged dogs running).

I was using a simple POSITION type approach but then I've recently rediscovered that POSITION doesn't honor collations (Tim had pointed it out but I'd misinterpreted or forgotten). In my selfish case since I use ANSI only its just the case-insensitive bit I'm bothered about

Roy

Roy,

<< Is there a possibility of a simple LIKE which ignores the single
character wildcard and doesn't use sliding windows? >>

I'll have to think about this.  The issue is always pollution of the syntax,
and having 3-4 different ways of searching text can be confusing.

Tim Young
Elevate Software
www.elevatesoft.com

Tim

><< Is there a possibility of a simple LIKE which ignores the single
>character wildcard and doesn't use sliding windows? >>
>
>I'll have to think about this. The issue is always pollution of the syntax,
>and having 3-4 different ways of searching text can be confusing.

The latter is precisely my problem. I can't use the standard LIKE syntax to search large CLOBs so I have to remember that I can use LIKE on the _Name column (VARCHAR(60) but not the _Notes column (CLOB), that I can use CONTAINS on a FTI column but not otherwise so I have four choices currently - two of yours and two of mine. I get very confused.

Roy