SQL returns no records, but should

[Scott Runkel]

> I.e. this line _could_(!) be a solution
>
> select * from oc_contact where lower(idname) like '%r,%h%'

Good idea, but '%MOST%likely%'  and '%most%likely%'  still return no records at 
all.

Also tried various combos like 'MOST likely', '%MOST likely', "most likely%' etc 
with different capitalization and location of the %.

-SR

Scott Runkel
Director of Technology
Hypix Media, Inc.
503-722-2123
----- Original Message ----- 
From: "Thorsten Hohage" <thohage at objectmanufactur.com>
To: "Valentina Developers" <valentina at lists.macserve.net>
Sent: Friday, July 06, 2007 12:19 PM
Subject: Re: SQL returns no records, but should


> 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
>
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
>