Instead of using COLLATE you can try using the "BINARY" option
instead. It will work in older versions of MySQL and I think it makes
you select a bit more readable. Just put "BINARY" before the
comparison you want to be case sensitive.
WHERE BINARY text LIKE '%Jehovah%'
On Nov 8, 2005, at 9:15 AM, Scott Hamm wrote:
I am running MySQL 5.0.15-nt on Windows 2000 PRO and use PHP as
front-end for word search in Bible. My goal is to set an option for
case sensitivity in text search. For example, if I want to search for
the word "Jehovah*" I would expect "Jehovah" not "JEHOVAH". This
query works for my objective:
SELECT
bo.book,
b.chapter,
b.verse,
b.text
FROM
avkjv.books bo
LEFT JOIN
avkjv.bible b ON b.book=bo.number
WHERE
text
COLLATE
latin1_general_cs
LIKE
CONCAT('%','Jehovah','%');
But if I want to turn case sensitivity off I tried to issue the query:
SELECT
bo.book,
b.chapter,
b.verse,
b.text
FROM
avkjv.books bo
LEFT JOIN
avkjv.bible b
ON
b.book=bo.number
WHERE
text
LIKE
CONCAT('%','Jehovah','%');
Without COLLATE function, and the result acted as if it is still case
sensitive. I tried to look up in mysql to no avail. How can I turn
'case sensitivity' off?
Thanks in advance,
Scott
--
Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
