How To Use LIKE In Oracle PL/SQL
March 31, 2023 | Oracle DBA |
This help is based on examples so it would be easier to understand. Oracle LIKE, LIKEC, LIKE2 and LIKE4 conditions in PL/SQL allows to match a text value using a pattern matching method. The conditions return all values who’s pattern is similar or if we are using NOT condition then NOT similar to the pattern. The syntax of Oracle Like is:
'<you_value1>' [NOT] LIKE '<your_value2>' [ ESCAPE '<your_escape_char>' ]
The first Oracle Like example has text “hello” and the Like condition will look for a text starting with letter “h” and the length is not important (“h%“). If the condition is true then return text “The Oracle Like returned TRUE” otherwise return “The Oracle Like returned FALSE“.
BEGIN
IF 'hello' LIKE 'h%'
THEN
DBMS_OUTPUT.PUT_LINE ('The Oracle Like returned TRUE') ;
ELSE
DBMS_OUTPUT.PUT_LINE ('The Oracle Like returned FALSE') ;
END IF;
END;
/
As the Dbms output shows the “hello” word is starting with the “h” letter and the condition returned the TRUE text. The next example is doing the same comparison but now the Like condition is looking for a sting starting with letter “e” (“e%“). The left side word has remained the same.
BEGIN
IF 'hello' LIKE 'e%'
THEN
DBMS_OUTPUT.PUT_LINE ('The Oracle Like returned TRUE') ;
ELSE
DBMS_OUTPUT.PUT_LINE ('The Oracle Like returned FALSE') ;
END IF;
END;
/
Since the “hello” word on left side doesn’t start with the “e” letter the condition is false and the output has text “The Oracle Like returned FALSE“.
The third script is showing the lines we are going to use with the following Oracle Like examples below and the Select statement in the cursor is used as a “source” query that generates 10 lines.
BEGIN
FOR rec IN (SELECT 'ABC'||rownum||'def' as text
FROM dual
CONNECT BY rownum < 11)
LOOP
DBMS_OUTPUT.PUT_LINE ('rec.text is ' ||rec.text) ;
END LOOP;
END;
/
This 4th example below will find all texts in the For Loop starting with ABC, so the condition looks “ABC%“. The “%“-character makes the Like condition to ignore the rest of string after the ABC letters.
BEGIN
FOR rec IN (SELECT 'ABC'||rownum||'def' as text
FROM dual
CONNECT BY rownum < 11)
LOOP
IF rec.text LIKE 'ABC%'
THEN
DBMS_OUTPUT.PUT_LINE ('rec.text is ' ||rec.text) ;
END IF;
END LOOP;
END;
/
The DBMS output above is not so different for the 3rd SQL query because all texts are starting with the ABC letters. Now lets do so amending in the Oracle Like condition and set new “%1def” condition on it. This condition is looking for strings ending with “1def” and as we are using the “%“-character in the beginning of the condition it doesn’t matter how the string starts or how long it is. The only thing that does matter is the “1def” end and not a letter after it.
BEGIN
FOR rec IN (SELECT 'ABC'||rownum||'def' as text
FROM dual
CONNECT BY rownum < 11)
LOOP
IF rec.text LIKE '%1def'
THEN
DBMS_OUTPUT.PUT_LINE ('rec.text is ' ||rec.text) ;
END IF;
END LOOP;
END;
/
The DDMS output has only 1 line that ends with “1def” and to make sure it’s correct take a look at the 3rd PL/SQL script’s output above.
The next Like example 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 Like condition the “%“-character is in the middle now. This way set rule means it is not important how many letters are long the string or what are the letters in the middle, while the strings start with ABC1 and end with def it will be suitable for the condition.
BEGIN
FOR rec IN (SELECT 'ABC'||rownum||'def' as text
FROM dual
CONNECT BY rownum < 11)
LOOP
IF rec.text LIKE 'ABC1%def'
THEN
DBMS_OUTPUT.PUT_LINE ('rec.text is ' ||rec.text) ;
END IF;
END LOOP;
END;
/
The last output has two lines and both lines have start and end letters as we set in the rule. Now on the next example we will become more specific about the condition and we will set the rule as all strings should start with “ABC1″ and end with “def“, but they should have only 1 character between them. The character could be anyone 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 script output below.
BEGIN
FOR rec IN (SELECT 'ABC'||rownum||'def' as text
FROM dual
CONNECT BY rownum < 11)
LOOP
IF rec.text LIKE 'ABC1_def'
THEN
DBMS_OUTPUT.PUT_LINE ('rec.text is ' ||rec.text) ;
END IF;
END LOOP;
END;
/
This Oracle Like example will have another strict condition. The strings can be only 7 characters long and as on the last example the text should start with “ABC” and end with “def” letters.
BEGIN
FOR rec IN (SELECT 'ABC'||rownum||'def' as text
FROM dual
CONNECT BY rownum < 11)
LOOP
IF rec.text LIKE 'ABC_def'
THEN
DBMS_OUTPUT.PUT_LINE ('rec.text is ' ||rec.text) ;
END IF;
END LOOP;
END;
/
The PL/SQL script above has returned almost all lines except one who has 8 characters.
The next examples are going to demonstrate Oracle Like and the escape character. To bring out the meaning of the escape character we will need to change the “source” SQL query in the script. Please take a look at the new output below. There are two lines ending with “%“-character and with the casual Like condition we wouldn’t be able to set those character into the criteria. They would be treated as any string.
BEGIN
FOR rec IN (SELECT 'ABC'||rownum||'def'||
DECODE (MOD (rownum,4),0,'%') AS text
FROM dual
CONNECT BY rownum < 11)
LOOP
DBMS_OUTPUT.PUT_LINE ('rec.text is ' ||rec.text) ;
END LOOP;
END;
/
On the following 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 the condition 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 Like condition is (‘ABC%\%’ ESCAPE ‘\’) is looking for strings that are starting with “ABC” and ending with the “%” character.
BEGIN
FOR rec IN (SELECT 'ABC'||rownum||'def'||
DECODE (MOD (rownum,4),0,'%') AS text
FROM dual
CONNECT BY rownum < 11)
LOOP
IF (rec.text LIKE 'ABC%\%' ESCAPE '\')
THEN
DBMS_OUTPUT.PUT_LINE ('rec.text is ' ||rec.text) ;
END IF;
END LOOP;
END;
/
The DBMS output contains only two lines that are starting with ABC and have the “%“-character in the end. If we have done the same condition without the escape character we would have all 10 lines in the output.
See Also:
Home