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,’);

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”