MySQL Wildcards
In MySQL support wildcard character to simulate any other character(s) in a string. Usually wildcard characters are used with the LIKE operator and LIKE operator is used in a WHERE clause to search for matching patter in string.
Note: Twotype of wildcards are used in conjointment with the LIKE operator:
% - The percent sign perform 1 or more characters
_ - The underscore perform a one character
Here are some examples showing different LIKE operators with '%' and '_' wildcards:
| LIKE Operator | Description |
| WHERE Address LIKE 'c%' | Finds field values starts with "c" |
| WHERE Address LIKE '%c' | Finds field values ends with "c" |
| WHERE Address LIKE '%and%' | Finds field values that have "and" in any position |
| WHERE Address LIKE '_2%' | Finds field values that have "2" in the second position |
| WHERE Address LIKE 'c%z' | Finds Finds values that starts with "a" and ends with "z" |
MySQL Using % (percent) wildcard Example 1
The following MySQL statement is used to search "address" start with "12-13" pattern matching:
Example
SELECT firstName, lastName, address FROM tblCustomerLoan
WHERE address LIKE '12-13%';
MySQL Using % (percent) wildcard Example 2
The following MySQL statement is used to search "address" ends with "Bhopal" pattern matching:
Example
SELECT firstName, lastName, address FROM tblCustomerLoan
WHERE address LIKE '%Bhopal';
MySQL Using % (percent) wildcard Example 3
The following MySQL statement is used to search "address" containing the pattern "civil":
Example
SELECT firstName, lastName, address FROM tblCustomerLoan
WHERE address LIKE '%civil%';
MySQL Using _ (underscore) wildcard Example
The following MySQL statement is used to search all customer with a "address" starting with any character and followed by "2":
Example
SELECT firstName, lastName, address FROM tblCustomerLoan
WHERE address LIKE '_2%';
MySQL Using [charlist] wildcard Example
The following MySQL statement is used to search all customer with a "address" starting with "2", "c", or "b":
Example
SELECT firstName, lastName, address FROM tblCustomerLoan
WHERE address LIKE '[2cb]%';
MySQL Using [charlist] wildcard Example
The following MySQL statement is used to search all customer with a "address" starting with "a", "b", "c" or "d":
Example
SELECT firstName, lastName, address FROM tblCustomerLoan
WHERE address LIKE '[a-d]%';
MySQL Using [!charlist] wildcard Example
The following MySQL statement is used to search all customer with a "address" NOT starting with "a", "b", "c" or "d":
Example
SELECT firstName, lastName, address FROM tblCustomerLoan
WHERE address LIKE '[!abcd]%';
OR
SELECT firstName, lastName, address FROM tblCustomerLoan
WHERE address NOT LIKE '[abcd]%';