SQL returns no records, but should

[Barry G. Sumpter]

Hi Scott,

That hurts my head as I spent lots of trial and error with the case
sensitivity issue.

Can you display the asc(mid(myField, 5,1)) to see what the character is?

I think a space is chr 32 as in display asc(" ")

Maybe is not a space?

Can you copy the "most likely" from the database into your code and try it?

Hth

Baz



-----Original Message-----
From: valentina-bounces at lists.macserve.net
[mailto:valentina-bounces at lists.macserve.net] On Behalf Of Scott Runkel
Sent: Saturday, 7 July 2007 5:08 AM
To: Valentina Developers
Subject: Re: SQL returns no records, but should

> 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.

Other ideas?

-SR

Scott Runkel
Director of Technology
Hypix Media, Inc.
503-722-2123
----- Original Message ----- 
From: "Barry G. Sumpter" <BarrySum at BigPond.Net.AU>
To: "'Valentina Developers'" <valentina at lists.macserve.net>
Sent: Friday, July 06, 2007 12:03 PM
Subject: RE: SQL returns no records, but should


> Hi Scott,
>
> I recall the LIKE clause being case sensitive.
> And perhaps the special characters % are supposed to be prefixed with an
> escape character like \ or \\?
>
> I wrote about this on my web page with a FolderScanner Using Valentina
> project on:
> Www.barrysumpter.com
>
>
> hth
>     Barry G. Sumpter
>
> -----Original Message-----
> From: valentina-bounces at lists.macserve.net
> [mailto:valentina-bounces at lists.macserve.net] On Behalf Of Scott Runkel
> Sent: Saturday, 7 July 2007 4:56 AM
> To: Valentina Developers
> Subject: SQL returns no records, but should
>
> Hi,
>
> Does anyone know why the following would return no records? I for a fact
> there
> are records with the phrase "most likely" or "MOST likely" in them.
>
> SELECT *, recid FROM main WHERE (item_text like '%MOST likely%' OR
> answer_text
> like '%MOST likely%' ) ORDER BY item_num
>
> There's obviously just some little thing I'm missing. I'm using Val 2 and
> Director MX 2004. Case-sensitivity is off.
>
> For comparison, the following returns plenty of records:
>
> SELECT *, recid FROM main WHERE (item_text like '%MOST%' OR answer_text
like
>
> '%MOST%' ) AND (item_text like '%likely%' OR answer_text like '%likely%' )
> ORDER
> BY item_num
>
> -SR
>
> Scott Runkel
> Director of Technology
> Hypix Media, Inc.
> 503-722-2123
>
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
> 


_______________________________________________
Valentina mailing list
Valentina at lists.macserve.net
http://lists.macserve.net/mailman/listinfo/valentina