This web page is based on examples to be easier to follow. The Conditions Oracle LIKE, LIKEC, LIKE2 and LIKE4 allow to match text value using pattern matching method. The condition returns values who’s pattern is similar or when using NOT condition then not similar. The Oracle Like syntax is:
'<you_value1>' [NOT] LIKE '<your_value2>' [ ESCAPE '<your_escape_char>' ]
This SQL query is the most simple Like condition where it is looking for a string starting with “o“. Since the word on another side of the condition is “oracle” and that is a positive match the query returns the “oracle” word.
SELECT 'oracle' FROM dual WHERE 'oracle' LIKE 'o%';
The second SQL query has still the “oracle” word on the left side but the Like condition is looking for a string starting with “r“. Since the word doesn’t match the Select statement doesn’t return anything.
SELECT 'oracle' FROM dual WHERE 'oracle' LIKE 'r%';
The following SQL query is showing the data we are going to use for the following Oracle Like examples and the Select statement is used as a “source” .
SELECT 'ABC'||rownum||'def' as text
FROM dual
CONNECT BY rownum < 11;
In this example below we will find all texts starting with ABC so the condition looks “ABC%“. The “%” character sets a condition to Like to ignore the characters after ABC string.
SELECT *
FROM
(SELECT 'ABC'||rownum||'def' AS text
FROM dual
CONNECT BY rownum < 11 )
WHERE text LIKE 'ABC%';
The output is not so different for the first query because all texts are starting with ABC letters. Lets amend the Oracle Like conditions and set “%1def“. This condition is looking for strings ending with “1def” and as we are using the “%” character in the beginning it doesn’t matter how the string starts or how long it is. The only thing that matters is the end “1def” and no more a letter after.
SELECT *
FROM
(SELECT 'ABC'||rownum||'def' AS text
FROM dual
CONNECT BY rownum < 11 )
WHERE text LIKE '%1def';
The SQL query returned only 1 lines that ends with “1def“.
The fifth example with Like in Oracle has set rules to the beginning and ending parts. The string have to start with “ABC1” and it should end with “def“. Please take a look at the SQL statement the “%” character is in the middle now. This way set rule says: it is not important how many letters are long the text or what are the letter while the string starts with ABC1 and ends with def it will be suitable for us.
SELECT *
FROM
(SELECT 'ABC'||rownum||'def' AS text
FROM dual
CONNECT BY rownum < 11 )
WHERE text LIKE 'ABC1%def';
The output has two lines and both has start and end as we set in the rule. Now if we want to be more specific and set the rule as the string should start with “ABC1″ and end with “def“, but it should have only 1 letter between them. The letter could be any possible letter but it should be only 1 letter be between them then the condition looks “ABC1_def“. With this condition the string cannot be longer than 8 characters and the query found only 1 line. See the result below.
SELECT *
FROM
(SELECT 'ABC'||rownum||'def' AS text
FROM dual
CONNECT BY rownum < 11 )
WHERE text LIKE 'ABC1_def';
Lets try another example with the strict Oracle Like condition as we had the last one. On this example below the text should start with “ABC” and end with “def” but it shouldn’t be longer than 7 characters.
SELECT *
FROM
(SELECT 'ABC'||rownum||'def' AS text
FROM dual
CONNECT BY rownum < 11 )
WHERE text LIKE 'ABC_def';
This output returned almost all lines except the 1 string that is 8 characters long.
The following examples are with Oracle Like and the escape character. To bring out the meaning of the escape character we will need to change a bit the “source” query. Please take a look at the new output below. There are two lines ending with “%” character and with the Like conditions above you wouldn’t be able to set those character into your condition. They would be treated as any character.
SELECT 'ABC'||rownum||'def'||DECODE (MOD (rownum,4),0,'%') AS text
FROM dual
CONNECT BY rownum < 11;
On this example below we are going to set “\” as the escaping character. This character is important to let the Like condition to know that a character after the escape character should be treated as a literal character and even it may have set a special meaning in Oracle system use it as a plain character. The following example would be possible without the escape character since the “%” character means ignore rest of the text and we got 2 lines ending with the “%” character. The rule we have set in the SQL query (‘ABC%\%’ ESCAPE ‘\’) is looking for string that starts with “ABC” letters and ends with the “%” character.
SELECT *
FROM
(SELECT 'ABC'||rownum||'def'||DECODE (MOD (rownum,4),0,'%') AS text
FROM dual
CONNECT BY rownum < 11)
WHERE text LIKE 'ABC%\%' ESCAPE '\';
The output contains the two only lines that started with ABC and had the “%” character in the end.
See Also:
Home