Find exactly text which contain column's value in Mysql?

Question

id   text_1          text_2
1    おはよう         おはよ
2    こんにちは       ちわー
3    大丈夫           さよなら
4    でんわしたい     でんわしよう

I have DB same above.

I want to search with input: おはよう大丈夫？

Expect result will match: id = 1 and id = 3.

Please help me how to query search in Mysql? Thanks you.

Allan · Answer 1 · 2017-11-07T03:01:21.923

0

The following SQL query will fetch your 2 ids for exact string matching

select id from TABLENAME where text_1 in ('おはよう','大丈夫');

You can also use like operator with % to fetch approximately strings id.

You might face encoding issue (文字化け）depending on your DB settings, so you might have to convert SJIS <-> UTF-8

Last but not least, if you want to use the full string as a comparison criteria to select the rows, then you can reuse the following code:

edited Nov 07 '17 at 03:01

answered Nov 07 '17 at 02:47

Allan

but my input is text include: おはよう大丈夫？ Not split 2 word おはよう and 大丈夫. – baohq Nov 07 '17 at 02:54
1

You have to split it somehow or how will you know which part goes in which field? – tadman Nov 07 '17 at 02:54
Yeah or you can implement your own comparison method based on hamming distances, or number of common char shared by the two strings – Allan Nov 07 '17 at 02:56
My input is same Chat text, maybe another way not need split it but still search above? – baohq Nov 07 '17 at 02:56
if you want to use the full string as a comparison criteria to select the rows, then you can reuse the following code: https://stackoverflow.com/questions/5322917/how-to-compute-similarity-between-two-strings-in-mysql – Allan Nov 07 '17 at 03:01
1

Thanks you very much. I will try this. – baohq Nov 07 '17 at 03:05

score 0 · Answer 2 · answered Nov 07 '17 at 03:03

0

select id from TABLENAME where text_1 REGEXP "おはよう大丈夫"

answered Nov 07 '17 at 03:03

cute_programmer

2 Answers2