ORACLE SQL
Special Operators| Character Functions | Number Functions | Date Functions
1. SPECIAL OPERATORS :
Used to filter data based on multiple values instead of using many OR
conditions.
IN / NOT IN
BETWEEN / NOT BETWEEN
Filters values within a given range and includes both start and end values.
SELECT *
FROM EMPLOYEES
WHERE SALARY BETWEEN 5000 AND 15000;
NOT IN
IS NULL / IS NOT NULL
Used to check whether a column contains empty values
SELECT * FROM EMPLOYEES WHERE EMAIL IS NULL;
SELECT * FROM EMPLOYEES WHERE AGE IS NOT NULL;
LIKE / NOT LIKE
Used for pattern matching in text using % and _ symbols.
• % Represents any number of characters (can be zero, one, or many
characters).
• _ Represents exactly one character only.
SELECT * FROM EMPLOYEES WHERE LASTNAME LIKE '_a%';
SELECT * FROM EMPLOYEES WHERE LASTNAME NOT LIKE '_a%';
CHARACTER FUNCTIONS
[Link]()
Converts text into uppercase.
SELECT FIRST_NAME, UPPER(FIRST_NAME) FROM EMPLOYEES;
[Link]()
Converts text into lowercase.
SELECT FIRST_NAME, LOWER(FIRST_NAME) FROM EMPLOYEES;
[Link]()
Capitalizes the first letter of each word.
SELECT INITCAP('oracle sql') FROM DUAL;
[Link]()
Used to join text values together.
SELECT CONCAT('Hi, ', FIRST_NAME) AS "Details" FROM EMPLOYEES;
[Link]()
Used to reverse the characters in a string.
SELECT FIRST_NAME, REVERSE(FIRST_NAME) AS "REVERSED NAME" FROM
EMPLOYEES;
[Link]()
Returns number of characters in a string.
SELECT LAST_NAME, LENGTH(LAST_NAME) FROM EMPLOYEES;
[Link]()
Extracts part of a string.
SELECT LAST_NAME, SUBSTR(LAST_NAME,1,3) FROM EMPLOYEES;
[Link]()
Used to get the position of a particular character in a string.
SELECT FIRST_NAME, INSTR(FIRST_NAME, 'e') FROM EMPLOYEES;
[Link]()
Used for left padding (adding characters on the left side).
SELECT SALARY, LPAD(SALARY, 6, '#') FROM EMPLOYEES;
[Link]()
Used for right padding (adding characters on the right side).
SELECT SALARY, RPAD(SALARY, 6, '#') FROM EMPLOYEES;
TRIM()
Used to remove extra spaces.
SELECT TRIM('Haleema') FROM DUAL;
REPLACE()
String-based replacement. Replaces a full substring (word or pattern) with
another substring.
SELECT REPLACE('I like SQL','SQL','Oracle') FROM DUAL;
TRANSLATE()
Character-based replacement. Replaces individual characters in a string with
corresponding characters from another set.
SELECT TRANSLATE('database', 'ae', '12') FROM DUAL;
NUMBER FUNCTIONS
ROUND()
Rounds number to nearest integer.
SELECT ROUND(345.48) FROM DUAL;
SELECT ROUND(345.98) FROM DUAL;
TRUNC()
Removes decimal part without rounding.
SELECT TRUNC(345.98) FROM DUAL;
POWER()
POWER() is used to raise a number to the power of another number.
SELECT POWER(2, 4) FROM DUAL;
ABS()
ABS() is used to convert a negative number into a positive number.
SELECT ABS(-100) FROM DUAL;
SIGN()
SIGN() is used to check whether a number is positive or negative.
• If the number is positive, it returns 1
• If the number is negative, it returns –1
SELECT SIGN(123) FROM DUAL;
SELECT SIGN(-123) FROM DUAL;
CEIL()
CEIL() is used to round a number upward to the nearest integer, even if the
decimal value is small.
SELECT CEIL(977.3734) FROM DUAL;
FLOOR()
FLOOR() is used to round a number downward to the nearest integer, even if
the decimal value is high.
SELECT FLOOR(977.6734) FROM DUAL;
DATE FUNCTIONS
MONTHS_BETWEEN()
Returns months difference between two dates.
SELECT MONTHS_BETWEEN(SYSDATE,'16-JUN-2023') FROM DUAL;
ADD_MONTHS()
Adds months to a given date.
SELECT ADD_MONTHS(SYSDATE,2) FROM DUAL;
NEXT_DAY()
Returns next occurrence of a weekday.
SELECT NEXT_DAY(SYSDATE,'SATURDAY') FROM DUAL;