Single Row Function in SQL

Price: INR 8,999.00
Price: INR 15,999.00
Was: INR 16,999.00

Let’s understand the Single Row Function in SQL. First, understand, What is Function?

A function is something which accepts the arguments perform an operation on these arguments and produce the result.

 

In SQL, We have two type functions.

Single-Row Function – Which returns one result per row. I.e accepts one row as arguments and produce the result in this row.

Group Function – Which returns one result per Set of rows. I.e accept multiple rows as the argument and produce only one result.

 

Single Row Function in SQL

The single row functions operate on single rows and return only one result per row. The arguments can be a user-supplied constant, variable, column name and an expression.

 

The single row functions are categorized into

  • Character Functions : Character functions accept character inputs and can return either character or number values as output.

  • Number Functions : Number functions accepts numeric inputs and returns only numeric values as output.

  • Date Functions : Date functions operate on date data type and returns a date value or numeric value.

  • Conversions Functions : Converts from one data type to another data type.

  • General Functions

 

Single-Row Function Examples

1. LOWER : The Lower function converts the character values into lowercase letters.

SQL> SELECT lower(First_Name) FROM Employees;

 

2. UPPER : The Upper function converts the character values into uppercase letters.

SQL> SELECT upper(First_Name) FROM Employees;

 

3. INITCAP : The Initcap function converts the first character of each word into uppercase and the remaining characters into lowercase.

SQL> SELECT initcap(First_Name) FROM Employees;

 

4. CONCAT : The Concat function converts the first string with the second string.

SQL> SELECT concat(‘Oracle’,’ Backup’) FROM DUAL;

 

5. SUBSTR : The Substr function returns specified characters from character value starting at position m and n characters long. If you omit n, all characters starting from position m to the end are returned.

Syntax: substr(string [,m,n])

SQL>  Select substr(‘ORACLE DATA RECOVERY’,6,8) FROM DUAL;

 

6. LENGTH : The Length function is used to find the number of characters in a string.

SQL> SELECT length(‘God is Great’) FROM DUAL;

 

7. LPAD : The LPAD function pads the character value right-justified to a total width of n character positions.

i.e LPAD (Salary, 10, ‘*’) —  Result will be – *****72000

SQL> SELECT LPAD(Salary,10,’*’) FROM Employees;

 

8. RPAD : The RPAD function pads the character value left-justified to a total width of n character positions.

i.e RPAD (Salary, 10, ‘*’) —  Result will be – 72000*****

SQL> SELECT RPAD(Salary,10,’*’) FROM Employees;

 

9. TRIM : The Trim function removes the leading or trailing or both the characters from a string.

i.e TRIM (‘S’ from ‘SeleniumBix’) —  Result will be – eleniumBix

SQL> SELECT trim(‘S’ FROM ‘SQL’) FROM DUAL;

 

10. REPLACE : The Replace function is used to replace a character with another character in a string.

Syntax: replace(column, old_char,new_char)

SQL> SELECT replace(‘ORACLE DATA BACKUP’, ‘DATA’,’DATABASE’) FROM DUAL;

 

11. ROUND : The Round function rounds the value to the n decimal values. If n is not specified, there won’t be any decimal places. If n is negative, numbers to the left of the decimal point are rounded.

i.e ROUND (45.98756, 2) —  Result – 45.98

 

12. TRUNC : The Trunc function truncates the value to the n decimal places. If n is omitted, then n defaults to zero.

i.e TRUNC(45.987, 2) —  Result – 45.99

     TRUNC(45.984, 2) —  Result – 45.98

 

13. MOD : The Mod function returns the remainder of m divided by n.

Syntax: mod(m,n)

i.e MOD(1600, 300) —  Result – 100

 

0 Comment

Leave a Reply