In this article, we will discuss operators in PL/SQL of Oracle PL/SQL tutorials. Here we have presented all operators and their examples.
If you missed the previous article of PL/SQL Constants and Literals, then please click here.
Lets start the tourβ¦.
Whatβs inside: Introduction to Operators Arithmetic operators Relational operators Comparison operators Logical operators String operators FAQs |
Introduction to Operators
An operator is a symbol that describes the compiler to perform specific mathematical or logical manipulation
PL/SQL language has various operators, and they are
- Arithmetic operators
- Relational operators
- Comparison operators
- Logical operators
- String operators

Arithmetic operators
Here the list of various arithmetic operators with an example
Operator | Description | Example |
---|---|---|
+ | Adds two operands | X + Y will give 15 |
– | Subtracts second operands from the first | X – Y will give 5 |
* | Multiplies both operands | X * Y will give 50 |
/ | Divides numerator by de-numerator | X / Y will give 2 |
** | Exponentiation operator increase one operand to the power of other | X ** Y will give 100000 |
Relational operators
Oracle has various relational operators. Here, the list of various Relational operators with an example:
Operator | Description | Example |
---|---|---|
= | Tests if the contents of two operands are equal or not, if yes, then condition becomes true. | (X = Y) is not true. |
!=<>~= | It will check if the values of two operands are equal or not else amounts (values) are not equal, then the condition becomes true. | (X != Y) is true. |
> | It is used to checks if the value of the left operand is greater than the value of right operand, if yes then condition becomes true. | (X > Y) is not true. |
< | It is used to check whether the left operand’s value is less than the value of right operand, if yes then condition becomes true. | (A < B) is true. |
>= | It is used to validate whether the value of the left operand is greater than or equal to the value of right operand, if yes then condition becomes true. | (A >= B) is not true. |
<= | It validates if the value of the left operand is less than or equal to the value of right operand, if yes then condition becomes true. | (A <= B) is true |

Comparison Operators
Here the some list of various Comparison operators with ans an example:
Operator | Description | Example |
---|---|---|
LIKE | The LIKE operator compares a character, string, or CLOB value to a pattern and returns TRUE if the value matches the pattern and FALSE if it does not. | If ‘Lucine Tech’ like ‘L% T_h’ returns a Boolean true, whereas, ‘Ritesh Singh’ like ‘R% S_h’ returns a Boolean false. |
BETWEEN | The BETWEEN operator tests whether a value lies in a specified range. x BETWEEN a AND b means that k >= a and k <= b. | If K = 10 then, k between 5 and 20 returns true, k between 5 and 10 returns true, but k between 11 and 20 returns false. |
IN | The IN operator tests set membership. k IN (set) means that x is equal to any member of set. | If k = ‘m’ then, k in (‘a’, ‘b’, ‘c’) returns Boolean false but k in (‘m’, ‘n’, ‘o’) returns Boolean true. |
IS NULL | The IS NULL operator returns the BOOLEAN value TRUE if its operand is NULL or FALSE if it is not NULL. Comparisons involving NULL values always yield NULL. | If k = ‘m’, then ‘k is null’ returns Boolean false. |
Logical Operators
Here the list of logical operators with an example:
Operator | Description | Examples |
---|---|---|
and | Called the logical AND operator. If both the operands are real, the-comparison N condition becomes true. | ( X and Y) is false. |
or | Called the logical OR Operator. If any of the two operands will true, then the condition becomes true. | (X or Y) is true. |
not | Called the logical NOT Operator and used to reverse the logical state of its operand. If a condition will true, then Logical NOT operator will make it false. | not (X and Y) is true. |
Strings
Oracle provides some string functions that help you manipulate character strings more effectively.
Function | Example | Result | Purpose |
---|---|---|---|
ASCII | ASCII(βAβ) | 65 | Returns an ASCII code. |
CHR(X) | CHR(β65β) | βAβ | Returns the character with the ASCII value of x. |
CONCAT | CONCAT(βAβ,βBCβ) | βABCβ | Concatenates the strings x and y and returns the appended string. |
CONVERT | CONVERT( βΓ Γ Γβ, βUS7ASCIIβ, βWE8ISO8859P1β ) | βA E Iβ | Convert a character string from one character set to another. |
INITCAP | INITCAP(βhi thereβ) | βHi Thereβ | Converts the first character in each word in a specified string to uppercase and the rest to lowercase. |
INSTR | INSTR( βThis is a playlistβ, βisβ) | 3 | Search for a substring and return the location of the substring in a string |
LENGTH | LENGTH(βABCβ) | 3 | Returns the number of characters in x. |
LOWER | LOWER(βAbcβ) | βabcβ | Return a string with all characters converted to lowercase. |
LPAD | LPAD(βABCβ,5,β*β) | β**ABCβ | Return a string that is left-padded with the specified characters to a certain length. |
LTRIM | LTRIM(β ABC β) | βABC β | Remove spaces or other specified characters in a set from the left end of a string. |
REGEXP_INSTR | REGEXP_INSTR( βY2K problemβ,β\d+β) | 2 | Return the position of a pattern in a string. |
REGEXP_LIKE | REGEXP_LIKE( βYear of 2017β²,β\d+β ) | true | Match a string based on a regular expression pattern. |
REGEXP_SUBSTR | REGEXP_SUBSTR( βNumber 10β, β\d+β ) | 10 | Extract substrings from a string using a pattern of a regular expression. |
Some more functions of strings in operators in pl/SQL
REPLACE | REPLACE(βJACK AND JONDβ,βJβ,βBLβ); | βBLACK AND BLONDβ | Replace all occurrences of a substring by another substring in a string. |
RPAD | RPAD(βABCβ,5,β*β) | βABC**β | Return a string that is right-padded with the specified characters to a certain length. |
RTRIM | RTRIM(β ABC β) | β ABCβ | Remove all spaces or specified character in a set from the right end of a string. |
SOUNDEX | SOUNDEX(βseaβ) | βS000β | Return a phonetic representation of a specified string. |
SUBSTR | SUBSTR(βOracle Substringβ, 1, 6 ) | βOracleβ | Extract a substring from a string. |
TRANSLATE | TRANSLATE(β12345β, β143β, βbxβ) | βb2x5β | Replace all occurrences of characters by other characters in a string. |
TRIM | TRIM(β ABC β) | βABCβ | Remove the space character or other specified characters either from the start or end of a string. |
UPPER | UPPER(βAbcβ) | βABCβ | Convert all characters in a specified string to uppercase. |
FAQs:
1. What is a PL/SQL block?
Answer:Β In Oracle PL/SQL, statements are grouped into units called Blocks. The PL/SQL blocks can include the constants, variables, SQL statements, loops, conditional statements, exception handling. These blocks can also build a procedure, a function, or a package.
2. What is a Join?
Answer: JoinΒ is a keyword used to query data from multiple tables based on the relationship between the fields of tables. Keys play a significant role in Joins.
3. Which operator has the highest precedence among the βAND, NOT, OR
Answer:NOT
4. What does the colon sign (: ) means in the given statement? : deficit:= balance β loan;
Answer: The colon (: )sign means here that the variable: the deficit is an external variable.
5. Write a single statement that adds the words βHelloβ and βWorldβ and assign it in a variable named greeting.
Answer: greet := βHelloβ || βWorldβ;
This is all about the operators in PL/SQL or PL/SQL operators. In the coming article, we will discuss conditions and loops. So, please stay with us.
Thank you:)
Nice
Very informative
Great
One another piece of the jewel added
Nice. Thanku. Very informative
These articles are helping a lot..Thanks
Thanks a lot
Good
Superb awesome
Very nice article…
Good content
Informative
Keep it up…
I like this. It’s worth bookmarking.
Informative
Very detailed and great article.
Great work with graphics..! more to be improved.. ! keep up the work!
Hi I read your post. You give very useful content for us.
Thanks
Useful info
Nice infoπ
Indeed a complete guide π
Useful
Good one π
I would like to thnkx for the efforts you’ve put in writing this website.
I’m hoping the same high-grade site post from you in the upcoming as well. Actually, your creative writing skills has encouraged me to get my own blog now. Really the blogging is spreading its wings rapidly and Your write up is a great example of it.
Informative
Very informative.
I adore reading through and I believe this website got some genuinely utilitarian stuff on it!
Spot on with this write-up, I honestly think this website needs much
more attention. I’ll probably be returning to read through more, thanks
for the info!
Your way of teaching is very good. Keep up the good work.
Helpful info. Fortunate me I found your website by accident, and I am stunned why
this coincidence did not took place in advance!
I bookmarked it.
Hello, I enjoy reading all of your post. I like to write a little comment to
support you.