Find data of table in string

Question

I want to find all the row in a table who have a specific column data contained in a given string.

Let's say i have a table of name.

+----------+------------------+
| id       |       name       |
+----------+------------------+
|    1     |       John Do    |
+----------+------------------+
|    2     | Douglas Rutledge |
+----------+------------------+
|    3     |   Herman Kelly   |
+----------+------------------+

For the given string : "I was in a party with Douglas Rutledge" I wanna get the result :

+----------+------------------+
| id       |       name       |
+----------+------------------+
|    2     | Douglas Rutledge |
+----------+------------------+

Or for the string : "I just met John Do and Herman Kelly" I wanna get the result :

+----------+------------------+
| id       |       name       |
+----------+------------------+
|    1     |       John Do    |
+----------+------------------+
|    3     |   Herman Kelly   |
+----------+------------------+

I imagine a query who could look like : SELECT * FROM my_table WHERE my_table.name IS CONTAINED IN "my_string"

@Ravi I'v edited my question, could you give me an exemple ? — Ethrak, Jun 17 '17 at 19:38
You're going to have a hard time with that. You will need to parse the entire string and somehow identify names in it. It would be a lot easier to do if you only accepted a direct name. E.g. `John Do, Herman Kelly, Will Smith` instead of something like `I really like the following actors, such as John Do, etc...`. If you do that, you will be able to use the LIKE operator. — SandPiper, Jun 17 '17 at 19:52

ScaisEdge · Answer 1 · 2017-06-17T19:41:22.110

0

You can use like eg for Do

select * from my_table 
where name like '%Do%';

for reverse you could try use

select * from my_table 
where   "I was in a party with Douglas Rutledge"   like concat('%,name, '%');

edited Jun 17 '17 at 19:41

answered Jun 17 '17 at 18:18

ScaisEdge

131,976
10
91
107

The problem is that i don't know if there is a name in the string that i got. I need to find a way to do the opposite and check if every row of a database got a name that is IN the string. Not the opposite – Ethrak Jun 17 '17 at 19:10
your comment have not sense to me .. update your question and a proper data sample and the expected result .. – ScaisEdge Jun 17 '17 at 19:18
Answer updated with a suggestion ...hope is useful – ScaisEdge Jun 17 '17 at 19:41

Ratan Phayade · Answer 2 · 2017-06-17T22:11:33.123

You can use full text search for this purpose.

In order to use full text search you must create a FULLTEXT index of the columns. To do that you can use this query.

ALTER TABLE <table_name> ADD FULLTEXT <index_name>(<column_names>);

once the fulltext index is created you can query something like this.

select 
     id, name
from 
     <table_name> 
where 
     match(<column_name>) against ('<string>')

for the : "I just met John Do and Herman Kelly". result will be :

 +----------+------------------+ 
 | id       |       name       |
 +----------+------------------+
 |    1     |       John Do    |
 +----------+------------------+
 |    3     |   Herman Kelly   |
 +----------+------------------+

for more info : https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html#function_match

MySQL has support for full-text indexing and searching:

A full-text index in MySQL is an index of type FULLTEXT.

Full-text indexes can be used only with InnoDB or MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXT columns.

For large data sets, it is much faster to load your data into a table that has no FULLTEXT index and then create the index after that, than to load data into a table that has an existing FULLTEXT index.

hope this helps

Could have done it but if i have 2 distinct people with the same first name, let's say : "John Do" and "John Carpenter". They will both be returned with the string : "I just met John Do and Herman Kelly" And if someone's name is "Just Do", he will come up too — Ethrak, Jun 18 '17 at 14:13

Find data of table in string

2 Answers2