Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL LIKE Operator      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: LIKE

Definition:
The Oracle PL/SQL LIKE operator is used to match or test a conditional term using a "wildcard search". Wildcard characters (operators) are used to create the search string. The two operators are the percent sign ('%') and the underscore ('_').

Percent Matching
The percent ('%') matches any group of characters. It can 'stand in' for zero or more characters, with no upper limit. Consider the string 'wood%' used as search term with LIKE. Because of the percent sign at the end of the term, the search term will match anything and everything after 'wood'. It will match on 'wood', 'wooded', 'woodland', 'woods', 'woods!', 'woods in the country' and so on.

It would not match on 'darkwood', 'redwood', or 'parkwoods' because the string starts with 'wood'.

If the search term was '%wood' then it could (and would) attempt to match anything before 'wood'. It would match on 'darkwood' and 'redwood', but not on 'parkwoods' (because of the 's' at the end of 'parkwoods').

The term '%wood%' used as search term would match ANY text with the word 'wood' in it. The leading and trailing percent signs tell the LIKE operator to basically match anything before and/or after the text 'wood', as long as it found 'wood' somewhere in the search text.

Underscore Matching
The underscore ('_') is more selective- it matches any single character.

Consider the string 'w__d' used as search term with LIKE. The two underscores in the middle tell the LIKE operator to look for a 'w', then any two characters, and then a 'd'.

This search term will match on 'wood', wild', 'wand', 'ward', and so on. It would not match on 'weird', 'wad', 'wide', or 'wed'. (There are either too few characters or too many to satisfy the match condition.)

Similarly, the search term 'wood_' will match on 'woods', 'woode', 'woody', 'wood!', and any other instance of 'wood' with one and only one additional character after it. It would not match 'wooded', 'woodland', 'redwood', or 'woodsman'.

Example Usage:

The SQL statement below displays any employee whose name starts with letter 'K':

SELECT ENAME, DEPTNO, SALARY 
FROM EMPLOYEES
WHERE ENAME LIKE 'K%'


The SQL statement below displays any employee whose starts with letter 'K' and ends with 'R':

SELECT ENAME, DEPTNO, SALARY 
FROM EMPLOYEES
WHERE ENAME LIKE 'K%R'

This will match (for example), 'KELLER', 'KRAMER", 'KOURNHAR', 'KLIPSAR', and so on.

The SQL statement below displays any employee whose name has six (6) letters, starts with letter 'K' and ends with 'R':

SELECT ENAME, DEPTNO, SALARY 
FROM EMPLOYEES
WHERE ENAME LIKE 'K____R'

This will match (for example), 'KELLER', 'KRAMER", 'KOOMAR', and so on. It would not match on 'KRASNER' or 'KERR' (too many or too few letters).

In cases where LIKE operator has to be used with numeric data, Oracle implicitly converts all numeric data into character type and does the comparison.

The SQL statement below displays any employee whose four-digit salary ends with '500':

SELECT * 
FROM EMPLOYEE
WHERE SALARY LIKE '_500'



Related Links:

Related Code Snippets:
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 215 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?