SQL returns no records, but should

[Thorsten Hohage]

Hi Scott,

On 2007-07-06, at 21:07, Scott Runkel wrote:

>> I recall the LIKE clause being case sensitive.
>> And perhaps the special characters % are supposed to be prefixed  
>> with an
>> escape character like \ or \\?
>
> Thanks, Barry.
>
> For clarification, I get perfect, identical results with "%MOST%"  
> and "%most%" which means that "%" seems OK and that the case- 
> sensitivity is not an issue.
>
> The problem is the phrases with a space in the middle: "%most likely 
> %" or "%MOST likely%" return nothing, but I can clearly see lots of  
> records with that phrase in them.

I just checked with a table in one of my databases

	select * from oc_contact where idname like '%r, H%'

and

	select * from oc_contact where idname like '%R, H%'

return different result sets, so a record with idname 'Maier, Helmut'  
was found with the first one, with the second not. This may be  
related to collation setting, but I'm not sure about. I always use a  
lower in my generated code.

Furthermore there could be different chars displayed on-screen as a  
blank, but not beeing chr(32), especially with Unicode-16, so perhaps  
replacing the blank with a third "%" could be a solution but of  
course then a term like "most not likely" would be returned, too.

I.e. this line _could_(!) be a solution

	select * from oc_contact where lower(idname) like '%r,%h%'


regards

Thorsten Hohage
--
objectmanufactur.com - Hamburg,Germany