Single row function in SQL are the ones who work on a single row and return one output per row.
Single row function in SQL can be character, numeric, date, and conversion functions. these functions are used to modify data items. These functions need one or more input and operate on each row, thereby returning one output value for each row.
SQL General Functions
NVL Function :The Oracle/PLSQL NVL function lets you substitute a value when a null value is encountered.
Examples:
1 2 3 | SELECT NVL(10,2) FROM DUAL; |
1 2 3 | SELECT NVL(NULL,'Oracle') FROM DUAL; |
1 2 3 | SELECT bdate, NVL(TO_CHAR(bdate),'There is no birth date!!!') FROM student; |
NVL2 Function: The Oracle/PLSQL NVL2 function extends the functionality found in the NVL function. It lets you substitutes a value when a null value is encountered as well as when a non-null value is encountered.
Examples:
1 2 3 | SELECT NVL2('Oracle','First','Second') FROM DUAL; |
1 2 3 | SELECT bdate, NVL2(TO_CHAR(bdate),'Birth Date is True','Birth Date ') FROM student; |
NULLIF Function: The Oracle/PLSQL NULLIF function compares expr1 and expr2. If expr1 and expr2 are equal, the NULLIF function returns NULL. Otherwise, it returns expr1.
Examples:
1 2 3 | SELECT NULLIF('Oracle','MYSQL') FROM DUAL; |
1 2 3 | SELECT NULLIF('MYSQL','MYSQL') FROM DUAL; |
COALESCE Function: The Oracle/PLSQL COALESCE function returns the first non-null expression in the list. If all expressions evaluate to null, then the COALESCE function will return null.
Example:
1 2 3 | SELECT COALESCE('DB Backup','Oracle') FROM DUAL; |
DECODE Function: The Oracle/PLSQL DECODE function has the functionality of an IF-THEN-ELSE statement.
Example:
1 2 3 4 5 6 | SELECT DECODE(supplier_id, 10000, 'Samsung', 10001, 'Nokia', 10002, 'Apple', 'Huaweii') FROM mytable; |
SQL Case Conversion Functions
LENGTH Function: The Oracle/PLSQL LENGTH function returns the length of the specified string.
1 2 3 | SELECT LENGTH (name),name FROM student; |
CONCAT Function: The Oracle/PLSQL CONCAT function allows you to concatenate two strings together.
Examples:
1 2 3 | SELECT CONCAT('Hello','World!') FROM DUAL; |
1 2 3 | SELECT CONCAT(CONCAT('Hello',' '),'World!') FROM DUAL; |
1 2 3 | SELECT 'Hello' || ' ' || 'World' FROM DUAL; |
1 2 3 | SELECT name || ' ' || lastname FROM student; |
SUBSTR Function: The Oracle/PLSQL SUBSTR functions allows you to extract a substring from a string.
Examples:
1 2 3 | SELECT SUBSTR('Hello World',3,5) FROM DUAL; |
1 2 3 | SELECT bdate, SUBSTR(bdate,7,4) FROM student; |
INSTR Function: The Oracle/PLSQL INSTR function returns the location of a substring in a string.
1 2 3 | SELECT INSTR('I like a cup of coffee','coffee') FROM DUAL; |
1 2 3 | SELECT INSTR('I like a cup of coffee, Do you like coffee','coffee',1,2) FROM DUAL; |
LPAD Function: The Oracle/PLSQL LPAD function pads the left-side of a string with a specific set of characters (when string1 is not null).
1 2 3 | SELECT LPAD('Hello',10,'-') FROM DUAL; |
RPAD Function: The Oracle/PLSQL RPAD function pads the right-side of a string with a specific set of characters (when string1 is not null).
1 2 3 | SELECT RPAD('Hello',10,'-') FROM DUAL; |
TRIM Function: The Oracle/PLSQL TRIM function removes all specified characters either from the beginning or the end of a string.
1 2 3 | SELECT TRIM(' HELLO ') AS TrimmedString from dual; |
RTRIM Function: The Oracle/PLSQL RTRIM function removes all specified characters from the right-hand side of a string.
LTRIM Function: The Oracle/PLSQL LTRIM function removes all specified characters from the left-hand side of a string.
1 2 3 4 | SELECT LTRIM(' HELLO ') AS TrimmedString from dual; SELECT RTRIM(' HELLO ') AS TrimmedString from dual; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SELECT LTRIM(' code') FROM DUAL; SELECT LTRIM(' code', ' ') FROM DUAL; SELECT LTRIM('000123', '0') FROM DUAL; SELECT LTRIM('123123code', '123') FROM DUAL; SELECT LTRIM('123123code123', '123') FROM DUAL; SELECT LTRIM('xyxzyyycode', 'xyz') FROM DUAL; SELECT LTRIM('6372code', '0123456789') FROM DUAL; |
REPLACE Function: The Oracle/PLSQL REPLACE function replaces a sequence of characters in a string with another set of characters.
1 2 3 | SELECT replace('ORACLE DATA BACKUP', 'DATA','DATABASE') FROM DUAL; |
1 2 3 | SELECT name, REPLACE(name,name,CONCAT('Name:',name)) FROM student; |
SQL Character Function
UPPER Function: The Oracle/PLSQL UPPER function converts all letters in the specified string to uppercase. If there are characters in the string that are not letters, they are unaffected by this function.
1 2 3 | SELECT name,UPPER(name),LOWER(name) FROM student; |
LOWER Function: The Oracle/PLSQL LOWER function converts all letters in the specified string to lowercase. If there are characters in the string that are not letters, they are unaffected by this function.
1 2 3 | SELECT name,UPPER(name),LOWER(name) FROM student; |
INITCAP Function: The Oracle/PLSQL INITCAP function sets the first character in each word to uppercase and the rest to lowercase.
1 2 3 | SELECT INITCAP('HELLO WORLD'), INITCAP('hello world') FROM DUAL; |
SQL Number Functions
ROUND Function: The Oracle/PLSQL ROUND function returns a number rounded to a certain number of decimal places.
Örnekler
1 2 3 | SELECT ROUND(123.67,1) FROM DUAL; |
1 2 3 | SELECT ROUND(123.67) FROM DUAL; |
1 2 3 | SELECT ROUND(123.67,-1) FROM DUAL; |
TRUNC Function: The Oracle/PLSQL TRUNC function returns a number truncated to a certain number of decimal places.
1 2 3 | SELECT TRUNC(123.67,1) FROM DUAL; |
1 2 3 | SELECT TRUNC(123.67) FROM DUAL; |
MOD Function: The Oracle/PLSQL MOD function returns the remainder of m divided by n.
1 2 3 | SELECT mod(24,7) FROM DUAL; |
SQL Date Functions
SYSDATE Function: The Oracle/PLSQL SYSDATE function returns the current system date and time on your local database.
1 2 3 | SELECT SYSDATE FROM DUAL; |
ADD_MONTHS Function:The Oracle/PLSQL ADD_MONTHS function returns a date with a specified number of months added.
1 2 3 | SELECT MONTHS_BETWEEN (TO_DATE ('2003/07/01', 'yyyy/mm/dd'), TO_DATE ('2003/03/14', 'yyyy/mm/dd') ) FROM DUAL; |
1 2 3 4 5 | SELECT MONTHS_BETWEEN(SYSDATE , TO_DATE(bdate)) FROM student WHERE bdate IS NOT NULL; |
MONTHS_BETWEEN Function: The Oracle/PLSQL MONTHS_BETWEEN function returns the number of months between date1 and date2.
LAST_DAY Function: The Oracle/PLSQL LAST_DAY function returns the last day of the month based on a date value.
NEXT_DAY Function: The Oracle/PLSQL NEXT_DAY function returns the first weekday that is greater than a date.
ROUND Function: The Oracle/PLSQL ROUND function returns a date rounded to a specific unit of measure.
TRUNC Function: The Oracle/PLSQL TRUNC function returns a date truncated to a specific unit of measure.
