Using Regular Expressions with MySQL queries to Search

Using Regular Expressions with MySQL queries to Search

Regular expressions are a powerful tooI as most of you will agree but how does one use it with SQL queries ? Recently I had a requirement where I had to write a query that searches for the word “test” in a description field. Usually we use MYSQL “LIKE” or “Full Text” index queries to find out records containing our search parameter, but that was not going to work in this case. The requirement here was that if we search the word “test”, it should return  the following matches. <space>test<space> test<space> <space>test test, test. test testing And it should also return the following matches :  testing rocks fattest tester fattest So here is what I did. CREATE TABLE IF NOT EXISTS `test_reg` ( `PK_ID` int(10) NOT NULL AUTO_INCREMENT, `search_text` varchar(100) COLLATE latin1_general_ci NOT NULL PRIMARY KEY (`PK_ID`) ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=10 ; INSERT INTO `test_reg` (`PK_ID`, `search_text`) VALUES (1, ‘test some’), (2, ‘testsomemore’), (3, ‘sujatatest’), (4, ‘testing’), (5, ‘testingsujata’), (6, ‘sujata test’), (7, ‘test, my dog is testing’), (8, ‘sujtatatesting test’), (9, ‘test’), (10, ”), (11, ‘test123’), (12, ‘    test  ‘), (13, ‘test.’), (14, ‘test,’);

Query for getting required output: SELECT * FROM `test_reg` WHERE `search_text` REGEXP ‘(^(test)[^A-Za-z])|([^A-Za-z](test)[^A-Za-z])|([^A-Za-z](test)$)|(^(test)$)’ where (^(test)[^A-Za-z]) returns something like  test<space><something> eg.”test some”, “test, my dog is testing”, “test123”, “test.”, “test,” etc ([^A-Za-z](test)[^A-Za-z]) returns something like <something><space>test<space><something> eg. ” test ” ([^A-Za-z](test)$) returns something like <space>test or test(not like <space>test<something> or test<something>) eg.”sujata test” and “sujtatatesting test” (^(test)$) returns only “test”