How To Use LIKE Condition In Oracle

Conditions Oracle LIKE, LIKEC, LIKE2 and LIKE4 allow to do matching with a text value using a pattern matching method. The conditions return values who’s pattern is similar or using NOT condition then NOT similar. The syntax of Oracle Like is:

'<you_value1>' [NOT] LIKE '<your_value2>' [ ESCAPE '<your_escape_char>' ]

The first SQL query is the most simple Like condition where it’s looking for a string starting with letter “o“. Since the word “oracle” is a positive match the query returns the “oracle” word.

 SELECT 'oracle'
  FROM dual
 WHERE 'oracle' LIKE 'o%';

This SQL query has still the same “oracle” word on the left side but the Like condition is looking for a string starting with letter “r” now. 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 with the Oracle Like examples below and the Select statement is used as a “source” query.

 SELECT 'ABC'||rownum||'def' as text
   FROM dual
    CONNECT BY rownum < 11;

This first example below will find all texts starting with ABC so the condition looks “ABC%“. The “%”-character makes condition Like to ignore the characters after ABC letters.

 SELECT *
   FROM
    (SELECT 'ABC'||rownum||'def' AS text
       FROM dual
        CONNECT BY rownum < 11 )
  WHERE text LIKE 'ABC%';

The output of the first example is not so different for the first SQL query because all texts are starting with ABC letters. Now lets do so amending in the Oracle Like condition and set “%1def“. This condition is looking for strings with ending “1def” and as we are using the “%“-character in the beginning of the string it doesn’t matter how the string starts or how long it is. The only thing that does matter is the end “1def” and no letter after it.

 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” and to make sure it’s correct take a look at the first Select statement output above.

The third example with Like in Oracle has set rules to the beginning and end. The strings have to start with “ABC1” and they should end with “def“. Please take a look at the SQL statement the “%“-character is in the middle now. This way set rule means it is not important how many letters are long the text or what are the letters while the strings start with ABC1 and end 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 last output has two lines and both have start and end as we set in the rule. Now we will become more specific about the condition and we will set the new rule as the string should start with “ABC1″ and end with “def“, but it should have only 1 character between them. The character could be any possible one but there can be only one be between the beginning and the end letters. The new condition looks “ABC1_def“. With this way the string cannot be longer than 8 characters and the query will find 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';

The another example with more strict condition can have only 7 characters long. On this example the text should start with “ABC” and end with “def“.

 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 one who has 8 characters.

The next examples are with Oracle Like and the escape character. To bring out the meaning of the escape character we will need to change the “source” query. Please take a look at the new output below. There are two lines ending with “%“-character and with the usual Like condition we wouldn’t be able to set those character into the criteria. 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 literally and even it may have assigned a special meaning in Oracle system it will use it as a plain character. The following example wouldn’t be possible without the escape character since the “%“-character means ignore rest of the criteria part and we got 2 lines ending with the “%“-character. The rule we have set in the SQL query (‘ABC%\%’ ESCAPE ‘\’) is looking for strings that start with “ABC” letters and end 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:
Oracle Home