Which of the following SQL wildcard symbols can be used to match one or more characters in conjunction with the LIKE operator?

SQL LIKE query Command

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 _

• Video Tutorial on LIKE Query with AND , OR NOT combinations.

.

Here is our table with all the records.

idnameclassmark

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%' idnameclassmark

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%' idnameclassmark

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' idnameclassmark

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.