Escape a character in 'where....like'

[#1]

Hi all,

I want to exclude from a LOAD those rows containing certain strings using for example:

WHERE not ([tCust Name] like '*closed*' or
[tCust Name] like '*deleted*' or
[tCust Name] like '*bad debt*');

I also want to exclude those that contain '***' but this is the wildcard character and so using:

like '*****'

is not valid. In SQL I would use ESCAPE to define an escape character and so the clause would be

like '*\*\*\**' Escape ''

but this is not valid.

How can I achieve this?

Regards,

Gordon

[#2]

.... forgot to say that I know I could have an initial load and use 'replace' to change '***' to something else, but is this the only/best way to achieve it?

[#3]

Hi,

I`m not sure about 'like' function, but just an suggestion y cant you try out the functions like wildmatch, match, etc...

- Sridhar

[johnw · Accepted solution]

WHERE not( index([tCust Name],'closed')
or index([tCust Name],'deleted')
or index([tCust Name],'bad debt')
or index([tCust Name],'***'))

[#5]

Hi,

Matching is no good in this case unfortunately as need to search based on wildcards but for 'wildcards':

wildmatch doesnt work as '***' evaluates to 'all' as it allows wildcards.

match is case sensitive

mixmatch doesnt allow wildcards

Index does achieve the result though, but I wonder if this would be more/less efficient than using a 'replace'.

Thanks both.

Regards,

Gordon

[johnw]

Index should be much more efficient. To do the index, it merely has to loop until it finds the characters in the original string. To replace, it has to do the exact same thing, plus then modify some bytes. Then you still have to do the wildmatch, so then it has to search through the field again looking for the given pattern. And just wildmatch would almost certainly be slower than index because the internal code would have to be more complicated to handle the more complicated functionality.

Still, even if the index approach is, say, ten times faster, this would still probably make only a negligible difference in the speed of a typical script. So I'd go with whatever makes more sense to you.