0

I want to search with LIKE operator in two column of a table. Column names are : person_first_name, person_last_name . And i found a way for the search two columns like one column. The way is columns must be CONCAT and search in the concated string but my values are Chinese language. I want to search in concated Chinese words. In earlier I have searched Chinese language with this way "COLLATE UTF8_GENERAL_CI" but that's not work in concated string. I have tried many method but i didn't get true result.

Example:

SELECT * FROM (`dm_account` a) WHERE CONCAT(a.person_first_name, ' ', a.person_last_name) COLLATE UTF8_GENERAL_CI LIKE '%ANY CHINESE CHARACTER%' ORDER BY `a`.`createdate` asc;

But this query does not return to me any record.

SELECT CONCAT(a.person_first_name, ' ', a.person_last_name) FROM dm_account WHERE id=2184;

This query does not show me combined words. But this user has got name and last name.

Anybody can help me? Thanks.

Mesuti
  • 878
  • 1
  • 13
  • 29

2 Answers2

1

Table alias a not defined additionally not require COLLATE UTF8_GENERAL_CI if column characterset is utf8 and collation is utf8_general_ci.

You can check this example

CREATE TABLE `person` (
  `id` int(11) NOT NULL,
  `first_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `last_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Records of person

INSERT INTO `person` VALUES ('1', '昌, 畅, 长 ', '富, 芙');

Query

SELECT  * FROM person WHERE CONCAT(first_name, ' ', last_name) LIKE "%昌, 畅, 长  富, 芙%"

Result

1   昌, 畅, 长     富, 芙
Mehmet Ali Uysal
  • 129
  • 1
  • 4
  • Thank you and I will add a description for case-sensitive usage because needs for search process. Something first name were Chinese character but last name typed with latin character or vice versa.. So for solve this problem I used LOWER function in first/last name like these: SELECT * FROM person WHERE CONCAT(LOWER(first_name), ' ', LOWER(last_name)) LIKE '%lowered_words%'; – Mesuti Sep 02 '15 at 12:15
  • 1
    You’re welcome, Yes that is a solution but you dont need `LOWER` in `CONCAT` function if first_name and last_name fields collation is `utf8_general_ci`. **ci** at the end of utf8_general_**ci** means **case insensitive**. – Mehmet Ali Uysal Sep 03 '15 at 18:01
0
SELECT CONCAT(person_first_name, ' ', person_last_name) 
FROM dm_account 
WHERE id=2184

u ve used table alias a but not defined it

M0rtiis
  • 3,676
  • 1
  • 15
  • 22