Search Marketing

   
   

Pattern Matching Using Like Statement

Thu Jan 13, 2011 4:57 pm
<<     >>
Comments: 5 Views: 3811

To find rows in a database table using “Like” or "Not Like" is very simple.

If we say the following is our database:

mysql> select * from Name;
+----+--------+---------------------+
| ID | Name | Date |
+----+--------+---------------------+
| 1 | Joe | 2001-02-12 00:00:00 |
| 2 | Jack | 2003-03-15 00:00:00 |
| 3 | John | 1980-05-15 00:00:00 |
| 4 | James | 1994-09-21 00:00:00 |
| 5 | George | 1996-12-11 00:00:00 |
+----+--------+---------------------+


Using Like we can find specific people on this list.

1.Find all names that begin with “J”.

mysql> select * from Name where Name like 'J%';
+----+-------+---------------------+
| ID | Name | Date |
+----+-------+---------------------+
| 1 | Joe | 2001-02-12 00:00:00 |
| 2 | Jack | 2003-03-15 00:00:00 |
| 3 | John | 1980-05-15 00:00:00 |
| 4 | James | 1994-09-21 00:00:00 |
+----+-------+---------------------+


2.Find all names that end with “k”.

mysql> select * from Name where Name like '%k';
+----+------+---------------------+
| ID | Name | Date |
+----+------+---------------------+
| 2 | Jack | 2003-03-15 00:00:00 |
+----+------+---------------------+


3.Find all names the have an “e” anywhere in their name.

mysql> select * from Name where Name like '%e%';
+----+--------+---------------------+
| ID | Name | Date |
+----+--------+---------------------+
| 1 | Joe | 2001-02-12 00:00:00 |
| 4 | James | 1994-09-21 00:00:00 |
| 5 | George | 1996-12-11 00:00:00 |
+----+--------+---------------------+

This same procedure can be used to select dates as well.

4.Find all dates in the 1990's.

mysql> select * from Name where Date like '199%';
+----+--------+---------------------+
| ID | Name | Date |
+----+--------+---------------------+
| 4 | James | 1994-09-21 00:00:00 |
| 5 | George | 1996-12-11 00:00:00 |
+----+--------+---------------------+

5. Using "Not Like" is the same process. Find all names where there is no "e" in the name.

mysql> select * from Name where Name Not Like '%e%';
+----+------+---------------------+
| ID | Name | Date |
+----+------+---------------------+
| 2 | Jack | 2003-03-15 00:00:00 |
| 3 | John | 1980-05-15 00:00:00 |
+----+------+---------------------+

 



Comments
Tue Mar 25, 2008 2:00 am
Name: vinod | Email: vinod attt planetwebsolution dottt com | Comment: hello sir,
i have three name field as first name last name and middle name, i have to search record from three of column.
i can search with a single name for example select * from where first_name like %dinesh% or middle_name like %dinesh% or last_name like %dinesh% .
i found the result but when i try to find out Dinesh Mathur. dinesh is first name and mathur is last name.
please give me solution

Tue Mar 25, 2008 10:45 amThere are a couple different ways to solve this problem.

1. you could have a select query, and then have additional selects that can sort out the relevant information.

2. or, you could search multiple fields in the same query:

select FirstName,LastName,MiddleName
from names
where
FirstName like '%".$value."%'
&& LastName like '%".$value2."%'
&& MiddleName like '%".$value3."%'
order by LastName, FirstName

Sun Apr 04, 2010 11:52 am
Name: | Comment:

Thu Jan 13, 2011 4:27 pm
Name: Soth | Comment: I would change the AND (&&) to OR (||)

select FirstName,LastName,MiddleName
from names
where
FirstName like '%dinnish%'
|| LastName like '%dinnish%'
|| MiddleName like '%dinnish%'
order by LastName, FirstName

Thu Jan 13, 2011 4:57 pmIf you use OR you will retrieve results that may contain more than the desired input. By using AND you eliminate any negative field information.

John Bill Todd
John Jim Todd

...they are two different people. AND will give you the specific result.

Name:
*
URL: http: (ex. cnn.com)
Comment:
*
Number:
Math (16 + 2)
* required
SubCategories
Books

© 2017 Christonium LLC

Christonium.com
|
Terms of Use
|
Privacy
ccc