Home   |   About   |   Terms   |   Contact    
Read & Learn
 

How to match a string in MySQL?

Web Design & Development

◄ All Articles


A new product.






Popular Google Pages:




This article is regarding How to match a string in MySQL?
Last updated on: .



◕ In MySQL we can match or check a string with two patterns. One is through SQL and other is through Regular expression .i.e REGEXP .

We use LIKE operator in the SQL pattern to match a string. Same as we also use REGEXP operator to match a string in Regular expression pattern.

Though SQL pattern is more standard and mostly used, but Regular expression pattern is more powerful.

Details are given bellow with code.

Match a string with SQL pattern in different way

Let, we have a table India . It contains a column named states .

Table name: India
Column name: states.

It contains the rows :

Assam
Mizoram
Meghalaya
Nagaland
Tripura



◕ 1 st way %nb

$RiyaButu = mysql_query("SELECT states FROM India WHERE states LIKE ' %nd ' " );

It will give us result:
Nagaland

Because:
%nd means that we want a result that ends with nd . It may contain any characters before nd.



◕ 2 nd way M%

$RiyaButu = mysql_query("SELECT states FROM India WHERE states LIKE ' M% ' " );

It will give us result :
Mizoram
Meghalaya

Because:
M% means that we want a result that starts with M . It may contain any characters after M.



◕ 3 rd way %la%

$RiyaButu = mysql_query("SELECT states FROM India WHERE states LIKE ' %la% ' " );

It will give us result :
Meghalaya
Nagaland

Because:
%la% means that we want a result in which la is in any position between start and end. It may contain any characters before and after la.



◕ 4 th way _ssam

$RiyaButu = mysql_query("SELECT states FROM India WHERE states LIKE ' _ssam ' " );

It will give us result :
Assam

Because:
_ssam means that we want a result in which only the first character may be anything. But the last characters must be as ssam . We can use this underscore sign _ to match any one (only one) unknown character. We can use it in any position. Also we can use it several times.
For example:
_s_am
A_ss_m
As_a_



◕ 5 th way %r_

$RiyaButu = mysql_query("SELECT states FROM India WHERE states LIKE ' %r_ ' " );

It will give us result :
Tripura

Because:
%r_ means that we want a result which contains any number of characters in the beginning of the r , but only one character at the last of the r .



◕ 6 th way Tripura

$RiyaButu = mysql_query("SELECT states FROM India WHERE states LIKE ' Tripura ' " );

It will give us result :
Tripura

Because:
It means that we want a result which is absolutely same as Tripura .

If it is a variable like:
$riyabutu = Tripura;
and our queries is

$RiyaButu = mysql_query("SELECT states FROM India WHERE states LIKE ' $riyabutu ' " );

Then it will give us the same result
Tripura

Please note: When we insert our data in database then sometimes some spaces added before and after our username or password etc due to many reasons. As a result when we search by the above queries then we will not find the result or we will get an error. This is really a bad situation to debug. I have also face this type of situations.

So, always follow the rules and when you face such kind of problem please check your database, whether some spaces are added before and after your values.



◕ 7 th way NOT LIKE

$RiyaButu = mysql_query("SELECT states FROM India WHERE states NOT LIKE ' %g% ' " );

It will give us result :
Assam
Mizoram
Tripura

Because:
It means that we want a result in which there should be no g between start & end.

Match a string with Regular Expression in different way

REGEXP means Regular Expression. It is a very powerful technique. It is also very powerful and useful for security purpose.

Let, we have a table India . It contains a column named states .

Table name: India
Column name: states.

It contains the rows :

Assam
Mizoram
Meghalaya
Nagaland
Tripura



◕ 1 st way ^M

$RiyaButu = mysql_query("SELECT states FROM India WHERE states REGEXP ' ^M ' " );

It will give us result :
Mizoram
Meghalaya

Because:
The sign ^M means that, find strings that begin with and only the particular M . This ^ sign is used only at the beginning.



◕ 2 nd way a$

$RiyaButu = mysql_query("SELECT states FROM India WHERE states REGEXP ' a$ ' " );

It will give us result :
Meghalaya
Tripura

Because:
The sign a$ means that, find strings that end with and only the particular a. This $ sign is used only at the end.



◕ 3 rd way .ripura

$RiyaButu = mysql_query("SELECT states FROM India WHERE states REGEXP ' .ripura ' " );

It will give us result :
Tripura

Because:
.ripura means that we want a result in which only the first character may be anything. But the last characters must be ripura . We can use this . (full stop) to match any one (only one) unknown character. We can use it in any position. Also we can use it several times.
For example:
T.ip.ra
Tr…ra
Tri…a



◕ 4 th way [aghilry]

$RiyaButu = mysql_query("SELECT states FROM India WHERE states REGEXP ' [aghilry] ' " );

It will give us result :
Assam
Mizoram
Meghalaya
Nagaland
Tripura

Because:
[aghilry] means that we want a result in which any one of the letters inside the [ ] i.e. a, g, h , i, l, r, y is there.

We can also use:

[a-z] : This means any character between a to z.

[A-Z] : This means any character between A to Z.

[0-9] : This means any character between 0 to 9.

[a-zA-Z0-9] : This means any character between a to z, A to Z and 0-9.

^[A-Z] : This means the starting character must be between A to Z.

[A-Z]$ : This means the end character must be between A to Z.

^[A-Z]$ : This means the starting character and the end character must be between A to Z.

[^A-Z] : This means anything but A to Z.

^$riyabutu$ : This means the string must be same as value of the variable $riyabutu.



◕ Hope this will solve many of your queries. All the best.



◕ Related articles:
► How to make XML Sitemap?
► Robots.txt Fetch Error
► How to find URL of my Twitter Account?
► Difference between .html & .htm
► List of wildcard used in MySQL
► MySQL match a string




Popular Google Pages:





Top of the page

Amazon & Flipkart Special Products

   


Top of the page