CODEWITHSUNDEEP
phpmysql
Poo123
Poo123

Reputation: 49

whole or partial string - for search function

Example: 

$test = 'My name is'

I would need to return records which contain: 'My name is' OR 'my' OR 'name' OR 'is'.

I'm pretty sure MySQL has some function or some regular expression for this type of task.

Upvotes: 0

Views: 41

Answers (3)

Bernd Buffen
Bernd Buffen

Reputation: 15057

You can also use FULLTEXT search in InnoDB like this:

SELECT * FROM search
WHERE MATCH(mytext) AGAINST('my name sample' IN BOOLEAN MODE);

Note

you must set ft_min_word_len in your my.cnf. default is set to 4

a description from FULLTEXT search is : https://mariadb.com/kb/en/mariadb/fulltext-index-overview/

create sample table

CREATE TABLE `search` (
  `id` INT(10) UNSIGNED NOT NULL,
  `mytext` TEXT,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `ft_mytext` (`mytext`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

show all and search

mysql> select * from search;
+----+-----------------------------------------------------+
| id | mytext                                              |
+----+-----------------------------------------------------+
|  0 | Sample text includes My name is and much more text  |
|  1 | Only name in it                                     |
|  2 | now is includet sample                              |
|  3 | nothing of them in string                           |
+----+-----------------------------------------------------+
4 rows in set (0,00 sec)

mysql> SELECT * FROM search
    -> WHERE MATCH(mytext) AGAINST('my name sample' IN BOOLEAN MODE);
+----+-----------------------------------------------------+
| id | mytext                                              |
+----+-----------------------------------------------------+
|  0 | Sample text includes My name is and much more text  |
|  1 | Only name in it                                     |
|  2 | now is includet sample                              |
+----+-----------------------------------------------------+
3 rows in set (0,00 sec)

mysql> SELECT * FROM search WHERE MATCH(mytext) AGAINST('sample nothing' IN BOOLEAN MODE);
+----+-----------------------------------------------------+
| id | mytext                                              |
+----+-----------------------------------------------------+
|  3 | nothing of them in string                           |
|  0 | Sample text includes My name is and much more text  |
|  2 | now is includet sample                              |
+----+-----------------------------------------------------+
3 rows in set (0,00 sec)

mysql>

Upvotes: 1

hassan
hassan

Reputation: 8288

you need to read more about pattern matching in Mysql

for example

SELECT * FROMtablewhere title REGEXP "some regex pattern"

for your case , it maybe some thing like this :

SELECT * FROM `table` where title REGEXP "(My|name|is)"

Upvotes: 1

B. Desai
B. Desai

Reputation: 16436

Use query as follow:

select * from tbl_name where name like '%my%' OR name like '%name%' OR name like '%is%';

Upvotes: 0

Related Questions