SQL LIKE query for matching pattern records with wildcard MySQL table

By using LIKE query we can match part of the full data present in a column. Here our search word need not exactly match.

Using Like Query with wildcard in different combinations, we can match our keyword with the pattern of the data present in columns.

The best way to use LIKE command is to apply it against a text or varchar field along with wildcard % or _

SQL LIKE query to use wildcards to partially match strings with combination of AND OR NOT

.

Here is our table with all the records.

id	name	class	mark
1	John Deo	Four	75
2	Max Ruin	Three	85
3	Arnold	Three	55
4	Krish Star	Four	60
5	John Mike	Four	60
6	Alex John	Four	55

We will apply the LIKE command here like this to our name field.

SELECT * FROM student WHERE name LIKE '%John%'

id	name	class	mark
1	John Deo	Four	75
5	John Mike	Four	60
6	Alex John	Four	55

The above result shows that we will get all the names where John word is present in the record. It can be any where. Please note the use of symbol % in the query. This will match any number of character even zero character before or after the word John. So we are getting the names those starts with John also. We may require the names which only starts with John not inside the name. In other words we want records starting with John only. In this case we will remove the use of % at the left of word John. Here is the SQL with LIKE command.

Read how Regular expression is used to Pattern matching

SELECT * FROM student WHERE name LIKE 'John%'

id	name	class	mark
1	John Deo	Four	75
5	John Mike	Four	60

We can see the result above list out all the names starting with name John. To display the records which does not have specific word John as the beginning we have change our LIKE sql command a little by changing the % position to the end of the word. Here we will allow any character even zero character to the left of the desired word not to the right

SELECT * FROM student WHERE name LIKE '%John'

id	name	class	mark
6	Alex John	Four	55

The above result have desired word John at the end only.

Matching string in name column starting with A and ending with n.

SELECT * FROM student WHERE name LIKE 'A%n'

Output

6	Alex John	Four	55	male

Here is a summary of string matching using % along with LIKE query

`'%John'`	Matches string ending with John
`'John%'`	Matches string starting with John
`'%John%'`	Matches string anywhere with John
`'j%n'`	Matches string starting with j and ending with n

Use of underscore ( _) as wildcard in string matching

We can use underscore as wildcard for one character space and use them along with LIKE statement and apply to table columns. For example we want to collect all the account numbers ending with 044 in a five digit account number field. Here is the query for this.

SELECT * FROM account_master WHERE acc_no LIKE '__044'

We have used two underscores in our query to tell that first two digits can be any thing and it should end with 044.

Underscores as wildcard can be used at any location but one can replace one character only. We can use more than one underscore also inside our query.

Using NOT with LIKE

SELECT * FROM student WHERE name NOT LIKE '%John%'

This way we can use LIKE command with many other commands to get desired output.

Case Sensitive query

As we have seen all the above cases are case insensitive. To match lower only or upper only cases we have to use binary command to make binary matching. Here is an example.

select * from student where name LIKE binary '%A'

Try the same query by not using binary inside it.

Name of the students having letter 'a' and letter 'e' ( without single quotes )

SELECT * FROM student WHERE name LIKE  '%a%' AND name LIKE  '%e%'

Find all courses from the Section table that start with the character, C, but do not have h, as the second character.

SELECT * FROM student WHERE name LIKE  'C%' AND name NOT LIKE  '_h%'

Searching keyword across multiple columns by using AND , OR

Keyword should present in both columns ( by using AND )

SELECT * FROM TABLE_NAME 
WHERE Column1 Like '%keyword%' AND Column2 LIKE '%keyword%'

Keyword should present in any columns ( by using OR )

SELECT * FROM TABLE_NAME 
WHERE Column1 Like '%keyword%' OR Column2 LIKE '%keyword%'

Same way the query can be extended to search across more than two columns.

LIKE with CONCAT

In the string supplied the name is matched. (Reverse way of matching)

SELECT * FROM student WHERE 
' Our best student is Mr  John Deo  of 5th Class'   
LIKE  CONCAT('%',name,'%')

CONCAT to join strings in Query

Limitation of Like query

Like condition has to match the entire data where as by using Regular Expression REGEXP we can match anywhere within the data

Displaying records using PHP Script

All above queries can be used by using PHP script. First the Script should connect to MySQL database and then records can be displayed.

Here is the sample code.

<?Php
require "config.php";

$count="SELECT * FROM student WHERE name LIKE '%John%'";

echo "<table>";
echo "<tr><th>id</th><th>name</th><th>class</th><th>mark</th></tr>";
foreach ($dbo->query($count) as $row) {
echo "<tr ><td>$row[id]</td><td>$row[name]</td><td>$row[class]</td><td>$row[mark]</td></tr>";
}
echo "</table>";
?>

Questions

What is the purpose of the "LIKE" operator in SQL?
How does the "LIKE" operator work in SQL?
What are the wildcard characters used with the "LIKE" operator in SQL?
Can the "LIKE" operator be used for case-sensitive or case-insensitive comparisons in SQL?
What is the difference between the "%" and "_" wildcard characters in the "LIKE" operator?
Can the "LIKE" operator be combined with other SQL clauses, such as WHERE or NOT, to create more complex queries?
Can you provide an example of using the "LIKE" operator to search for patterns within a string column in SQL?
Are there any performance considerations when using the "LIKE" operator in SQL, especially with large datasets?
Can the "LIKE" operator be used with numeric or date columns in SQL? If yes, provide an example.
Are there any alternatives to using the "LIKE" operator in SQL for pattern matching?

Locate command is used to search for records having a string in any place LIKE query is used in keyword search script
Read how LIKE command is used in MSSQL database

Subscribe to our YouTube Channel here

navid	11-05-2009
I wonder if I can use '%' as below for LIKE command: declare X nvarchar declare Y nvarcahr select * from dbo.~ where(X '%' like Y)-- <-this line is mentioned

manoj	06-06-2009
hey all I am using like command for accesing name as alphabetics I have written for that as select * from tbl)country where country like " Textbox1.text " but it is not working properly please guide me

Alex	08-06-2009
### to manoj SELECT * FROM TABLE_NAME WHERE country like '%HERE_GOES_MASK%' In that query, you should replace the TABLE_NAME with the name of the table you query data from. Also, HERE_GOES_MASK should be changed to the string you want to find

Noel	04-07-2009
@manoj - This is how your command should be: "Select * from tblcountry where country like '%" Textbox1.Text "%'" OR you could also remove the "%" before the Textbox1.Text or remove it after, it will depend on your usage. Hope this helps.

kichu	16-07-2009
How will i get names of all those people who have the string 'in' in their names?

Penny	27-08-2009
I need to find a specific literal in an Oracle table column which is described as a "long" datatype. How do I do this, since "like" command can't be used on "long" type columns (can't use "long" column in a where clause of a select statement)? I get the error message:"inconsistent datatypes: expected NUMBER got LONG". Please help!

Indranil	12-11-2009
I want to selet more than one row at a time ...I think query should be... SELECT * FROM TABLENAME WHERE FIELDNAME LIKE 'A%' But it selecting only the first one ...I 'm working in MSAccess...with Core JAVA-Swing Can u please help me SIR..........

Deepak	03-12-2009
To Kichu.. select * from Table_Name where first_name like '%in%'

Nar	03-12-2009
Question PHP/SQL: I have an EXEC that take all my contact list from my Database and put them into a variable. Now I want to select all contact starting with "A" as the last name. And show them into my contact list. I want to do it with all letters "A" , "B" , "C" ... Something like Iphone Contact list , all familly name are in alphatical order. So, can I select and show all name starting by a specific letter? how? thx for answerying

Regalla Naresh Reddy	21-12-2009
Hi...! The information is useful. I need somehelp. I want to get the information like SELECT * FROM employee WHERE name LIKE '%ram' where '%ram' is the value of other table which is similar to the value in the current table. Means i need to retrieve the information from the current table based up on the value of other table. Can you help how to acheive this .....?

Perwez Akhter	07-01-2010
Dear all, Hi! Can I run two like in single command line. If Yes Tell me how

Shah Viral	13-01-2010
Hello EveryOne i want to find the Partucular Person name, WHen i write Down in TextBox .. Thanks

Kingsonprisonic	30-01-2010
Like not working in vb 6.... please help code: Me.Data1.RecordSource = "select * from phone where name LIKE '% " Me.Text1.Text "%'" Me.Data1.Refresh all >,<,>=,<=,and,or,between etc works correctly but like not working..... what can i do?

sf09	03-03-2010
what if i would like to find person's name which contain 2 words only?

smo	21-12-2009
select t1.field1,t1.field2,t2.field1.t2.field2 from t1,t2 where t1.keyfield=t2.keyfield and t2.name like '%Ram' Here t1 and t2 and two tables and linked by a keyfeild ( t1.keyfield=t2.keyfield)

smo	13-01-2010
You can run two like commands by using AND or OR combinations.

Designer	21-01-2010
how can i use like query to search for two words (statement) not only when word example : select # from table_name where column like'% hello world%'

a13	26-01-2010
"SELECT * FROM video WHERE category LIKE '%'."$_GET['category']".'%' ORDER BY video_id DESC LIMIT ...... I have no idea where is the problem but it seems that this sql can not work with this statement because the webpage is not loading after this statement can someone tell me where is the problem I know that get[category] is funny or animal

smo	26-01-2010
Try by printing the SQL to see what the value of category you are getting. You can also print the error message to see what has gone wrong.

manohar	04-02-2010
I want to check 4th digit of the account no is zero using sql query

Dimitree	24-02-2010
I like to search a general one suppose not the Exact one for that what query is use for Eg SELECT * FROM [reg1] WHERE ([Name] LIKE '%' @Name '%' ) This will display the name what u search in database I need the query for one are more search wht is that

Chandrasekaran	09-03-2010
Hi, I am having the table with the column First Name. Where ever the first name contains 'is' I just wanted to remove these word in that record.

Richard	16-04-2010
HI all, I have 3 tables (A,B,C)and 2 querys in the first query I join two tables (A

praveen	29-04-2010
hi, thanks for the submission of this since this has helped me a lot... i was confused with the usage of % and _. but now its clear for me... thanks again... hope i will be more benefitted in future from here

SQL LIKE query Command