Oracle Wildcard Characters

Oracle 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: Two type 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"

Oracle Using % (percent) wildcard Example 1

The following Oracle statement is used to search "address" start with "12-13" pattern matching:

Example

SELECT firstName, lastName, address FROM tblCustomerLoan 
WHERE address LIKE '12-13%';

Oracle Using % (percent) wildcard Example 2

The following Oracle statement is used to search "address" ends with "Bhopal" pattern matching:

Example

SELECT firstName, lastName, address FROM tblCustomerLoan 
WHERE address LIKE '%Bhopal';

Oracle Using % (percent) wildcard Example 3

The following Oracle statement is used to search "address" containing the pattern "civil":

Example

SELECT firstName, lastName, address FROM tblCustomerLoan 
WHERE address LIKE '%civil%';

Oracle Using _ (underscore) wildcard Example

The following Oracle 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%';

Oracle Using [charlist] wildcard Example

The following Oracle 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]%';

Oracle Using [charlist] wildcard Example

The following Oracle 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]%';

Oracle Using [!charlist] wildcard Example

The following Oracle 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]%';

« Previous