Date type issue with Like clause Arabic string in mysql

Question

I work on old system, and the search in popup was made dynamically. for example if popup fields: field1, field2, field3 and the user type كلمة in the textbox for search or any arabic word. the system will create a dynamic search condition like this:

field1 LIKE '%كلمة%' OR field2 LIKE '%كلمة%' OR field3 LIKE '%كلمة%'

the problem occur when one of these fields is date type, in this case give me mysql error:

illegal mix of collations for operation 'like'

when I change the the word I search to be English word, it give me warning but work correctly because I don't need the field of date type in this situation (I search for text). also I can't change the core of this dynamic search php function because all system was built, so I can't use like this code:

OR DATE_FORMAT(field1, '%Y-%m-%d') LIKE '%كلمة%'

because I don't know which field is a Date type, it is a dynamic search.

also this issue occur on the hosting server only, and not on my localhost, my host is: Server version: 5.5.47-cll - MySQL Community Server (GPL) and my localhost mysql version is: 5.5.5.

this is show create table script:

CREATE TABLE `POS_QUOTATIONS` (
  `DEPT_ID` varchar(20) NOT NULL,
  `BILL_TYPE` int(11) NOT NULL,
  `BILL_NUMBER` int(11) NOT NULL,
  `BILL_NUMBER_TO_RTRN` int(11) NOT NULL,
  `SALESPERSON_NAME` varchar(255) NOT NULL,
  `BILL_DATE` date NOT NULL,
  `POS_ID` int(11) NOT NULL,
  `STOCK_ID` varchar(20) NOT NULL,
  `CURRENCY_ID` varchar(3) NOT NULL,
  `EXCHANGE_RATE` float NOT NULL,
  `SALESPERSON_TYPE` int(1) NOT NULL,
  `SALESPERSON_ID` int(10) NOT NULL,
  `CLIENT_TYPE` int(1) NOT NULL,
  `ACC_CODE` varchar(20) NOT NULL,
  `CLIENT_NAME` varchar(255) NOT NULL,
  `CLIENT_APPROVAL_NUMBER` varchar(20) NOT NULL,
  `NOTES` varchar(255) NOT NULL,
  `BILL_VALUE_BEFORE_DISCOUNT` double NOT NULL,
  `OVRALL_COST` double DEFAULT '0',
  `SALES_TAX` double NOT NULL,
  `ADITIONAL_AMOUNT` double NOT NULL,
  `DISCOUNT_PERCENTAGE` double NOT NULL,
  `DISCOUNT_AMOUNT` double NOT NULL,
  `BILL_VALUE_AFTER_DISCOUNT` double NOT NULL,
  `CLIENT_PAYMENT` decimal(13,2) NOT NULL,
  `REMAINING_AMOUNT` decimal(13,2) NOT NULL,
  `PAYMENT_METHOD` int(11) NOT NULL,
  `CREDIT_CARD_DETAILS` varchar(255) NOT NULL,
  `BANK_OPERATION_NUMBER` varchar(50) NOT NULL,
  `BANK_DETAILS` varchar(255) NOT NULL,
  `BANK_ACC_CODE` varchar(20) NOT NULL,
  `PAYMENT_METHOD_INFO` varchar(500) NOT NULL,
  `CONTRACT_PRODUCT_DETAILS` text NOT NULL,
  `CONTRACT_REC_VOUCHER_NO` varchar(255) NOT NULL,
  `CONTRACT_REPAYMENT_DATE` varchar(255) NOT NULL,
  `ROWID` varchar(255) NOT NULL,
  `YEAR` int(4) NOT NULL,
  `CREATION_USER` varchar(10) DEFAULT NULL,
  `CREATION_TIMESTAMP` varchar(14) DEFAULT NULL,
  `CREATION_COMPUTER_NAME` varchar(50) DEFAULT NULL,
  `LASTUPDATE_USER` varchar(10) DEFAULT NULL,
  `LASTUPDATE_TIMESTAMP` varchar(50) DEFAULT NULL,
  `LASTUPDATE_COMPUTER_NAME` varchar(50) DEFAULT NULL,
  `TRANS_FLAG` varchar(10) DEFAULT NULL,
  `RECORD_STATUS` varchar(1) DEFAULT NULL,
  `JOURNAL_STATUS` tinyint(1) DEFAULT NULL,
  `PROCESS_NUMBER` varchar(50) DEFAULT NULL,
  `OFFER_TERMS` text,
  `TECHNICAL_SPECIFICATIONS` text,
  `OFFER_STATUS` tinyint(1) DEFAULT NULL,
  `PROJECT_DESCRIPTION` varchar(500) DEFAULT NULL,
  `POS_CONTRACT_AGREEMENT_ID` varchar(50) DEFAULT NULL,
  `COST_CENTER_CODE` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`ROWID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

and whatever the charset of database on the hosting server it produce this error when charset is utf8_general_ci or latin1_swedish_ci thanks in advance.

Answer 1

When you use like on Date fields, they treated as latin strings. Assuming you are using utf8 charset, you need to convert date column before like it:

convert(field1 using utf8) LIKE '%كلمة%' OR convert(field2 using utf8) LIKE '%كلمة%' OR convert(field3 using utf8) LIKE '%كلمة%'

Ideally, if you add some logic here and conditionally apply conversion to date fields only. E.g. if you know that field2 is the only date field, you can build the query:

field1 LIKE '%كلمة%' OR convert(field2 using utf8) LIKE '%كلمة%' OR field3 LIKE '%كلمة%'