Pattern Matching in mysql

Pattern Matching in mysql

My sql provides two functions LIKE and NOTLIKE for Simple pattern matching

LIKE

Syntaxt for using like is expr

LIKE <pattern >

The Like provides '%' and _ as patterns

For example consider following table

sampletable
---------------------------------
|fname | lname |
------------------------------
| Joban | John |
-------------------------------
| Neena |T |
-------------------------------
| James| Mathew |
-------------------------------
|sreejitha|M |
-------------------------------
| Nitha | sree |
------------------------------

different types of patters using like


SELECT * FROM sampletable WHERE fname LIKE 'J%';

This will select first and 3rd row from the above table .

---------------------------------
|fname | lname |
------------------------------
| Joban | John |
-------------------------------
| James| Mathew |
------------------------------


SELECT * FROM sampletable WHERE fname LIKE '%J%';

output

---------------------------------
|fname | lname |
------------------------------
| Joban | John |
-------------------------------
| James| Mathew |
-------------------------------
|sreejitha|M |
-------------------------------

Next we can use five '_' to get records in which fname has 5 letters
SELECT * FROM sampletable WHERE fname LIKE '_____';

output

---------------------------------
|fname | lname |
------------------------------
| Joban | John |
-------------------------------
| Neena |T |
-------------------------------
| James| Mathew |
-------------------------------
| Nitha | sree |
------------------------------

REGEXP :

It matches a pattern with regular expression

Following is the pattern which can be used along with REGEXP

* a '.' can be used for any single character.

* s set of character in "[...]" matches any character within the brackets.

* "*" matches zero or more instances of the thing preceding .

* can use "^" at the beginning or "$" at the end of the pattern.

* + 1 or more

* ? 0 or 1

* {n} exactly n

* {n,} n or more

* {n,m} between n and m

* | either, or

* [^...] Any character not listed between the square brackets


some sample queries are shown below.

* for the names containing a "j" we can use the query
SELECT * FROM sampletable WHERE fname REGEXP 'j';

* for the names beginning with J we can write a query as follows
SELECT * FROM sampletable WHERE fname REGEXP '^J';

* for the nameending with 'a' we can write a query like
SELECT * FROM sampletable WHERE fname REGEXP 'a$'

* for the name with four characters we can use the query
SELECT * FROM sampletable WHERE fname REGEXP '^....$';
(or)
SELECT * FROM sampletable WHERE fname REGEXP '^.{4}$'

* Query to find all the names starting with a vowel and ending with 'y'
SELECT * FROM sampletable WHERE fname REGEXP '^[aeiou]|y$';





Enjoy :)