hi all,

I connect o FMP8.5 using the provided ODBC driver.

If I run an SQL statement like this...

"select * from contacts where firstname like 'Tim'"

it will only find "Tim", not "tim" or "TIM"
which is wrong.

the equals sign can be a binary value compare (therefore case
sensitive)
but the "like" compare should be a text compare (therefore NOT case
sensitive).

Whose problem is this? FMP or that datadirect crowd?
Who can I moan to?

A rediculous work around I use it to create a calc fields that makes
the firstname field all uppercase, then make sure the search criteria
is also forced to uppercase. But I should have to do it.

Any tips?

thanks

Tim

Try:

select * from contacts where firstname like '%Tim'

Note the single quotes (technically an apastrophe) instead of the double
quotes around the literal, and the percentage sign in front of it.

I had success testing with:

SELECT s_First, s_Last, n_SNName
FROM NamesForODBC where s_First like '%Rae'


Where I have two records with the same string, 'Rae', in NamesForODBC, the
above query returns both if they both use an upper-case 'R' and only the one
if I lower the case of one to 'r'.

In general, I've found the syntax to often be unexpected and fussy, and
hardly the 1992 ANSI standard. I believe, but do not know for certain, that
the driver vendor is at fault. There is another driver from Actual
Technologies (?), which I have never tried but about which I have heard good
anecdotal results.

--
John Weinshel
Datagrace
Vashon Island, WA
(206) 463-1634

Certified For Filemaker 8
Certified For Filemaker 7


<Citizen10Bears (AT) gmail (DOT) com> wrote

Apologies-- I mis-read your post, and I now realize you want the query to
not be case-sensitive. Looks like you are stuck with workarounds like what
you describe or using OR.

--
John Weinshel
Datagrace
Vashon Island, WA
(206) 463-1634

Certified For Filemaker 8
Certified For Filemaker 7


"John Weinshel" <john (AT) datagrace (DOT) biz> wrote

Try this SELECT * FROM WHERE LOWER(fieldname) LIKE LOWER('Tim')

To be precise,

"select * from contacts where LOWER(firstname) like LOWER('Tim')"

apache wrote:

In article <1162642323.718151.231330 (AT) b28g2000cwb (DOT) googlegroups.com>,
Citizen10Bears (AT) gmail (DOT) com wrote:

Recently, I've been working with FMP 6 to query a humongous Oracle DB
and it's working better than I dreamed it could. And fast, too.
However, I ran into the same problem as you. The big DB is running on
Oracle 9i and queries to it are case sensitive. So I have to use OR
statements:

SELECT RESULT_VALUE FROM BIG_FAT_TABLE
WHERE RESULT_NAME ='DIAGNOSIS' AND
(RESULT_VALUE LIKE '%LowerSearchTerm%' OR RESULT_VALUE LIKE
'%UpperSearchTerm%' OR RESULT_VALUE LIKE '%TitleSearchTerm%' )

SearchTerm is the FMP field into which the user enters the search term
and LowerSearchTerm, UpperSearchTerm, and TitleSearchTerm are FMP
calculation fields that are derived from it.

The queries are generated in FMP by concatenating global fields
containing the "static" parts of the query and "variable" fields like
SearchTerm.

I recently discovered that Oracle 10g has the statement: REGEXP_LIKE
which allows case insensitive queries. So I guess I'll have to keep
using those calculation fields until we upgrade Oracle.

This suggests that the case sensitive problem are that fault of the data
source so I assume this means that FMP 8.5 - when used as a data source
- does not support case insensitive searches unless it has a term like
REGEXP_LIKE.

By the way, here's a hint for those who are getting started with ODBC
and SQL queries: Use Crystal Reports to generate the queries then copy
and paste them into FMP where you can play with them and figure them
out. In Crystal Reports, you just drag tables and fields into a
"workspace" (and also use the "Select Expert") and CR automatically
generates the query for you. The only downside is that the queries CR
generates are littered with double quotes. However, you can strip the
double quotes out easily with the Substitute operator in FMP.

Bill

Bill