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