In my previous articles I have given the information about how to start preparing for OCA certification with real examples. In this article I would like to give and explain you features of single row functions with multiple real examples of it. Those examples are useful in OCA preparation. In this article we need to add features We are retrieving data from SQL tables. But sometimes we require to use the different functions to achieve the specified result.
What will you get from this article?
- What is mean by functions?
- Single Row Functions with examples
- Features of Single row functions
What is mean by functions in SQL?
The functions are nothing but subprograms which are used to retrieve the specified data from the table. There are multiple in-built functions which are used to achieve it. For retrieving the data, we need to pass the arguments or give the inputs to the function. The result of function is the return value.
The functions are most important in SQL to retrieve the specific data. There are two types of functions in SQL:
- Single Row Function
- Multiple row Function
Features of Single Row Functions :
The single row functions are functions which will give the results in single value. The single row functions will return only one value as a result. We need to know the features of the single row functions before we go ahead and check examples.
There are following features of Single Row Functions :
1.The single row functions are the functions which takes multiple inputs and returns only one value.
2.The single row functions are used to manipulate the data from database.
3.The single row functions act on the specific row that is returned.
4.The single row function returns one result per row.
5.The single row functions may modify the datatypes and can be nested.
6.The single row function accepts arguments that can be column or value.
Function_name(argument_1…argument..n);
Single row function examples:
In this article I would like you to give you multiple examples of Single row function. I would like to give you one simple and one difficult example of each function so that you can understand that easily.
Scenario 1 : Case Conversion functions
- Lower : The function converts data in Lowercase.
Examples :
Select Lower(‘Amit’) from Dual;
Output :
Amit
2.Upper: The function converts data in Uppercase.
Examples :
select UPPER(‘Amit’) from Dual;
Output :
AMIT
3.Initcap : The first character will be uppercase and all other in Lowercase.
Example :
Select Initcap(‘amit’) from dual;
Output :
Amit
Scenario 2 : The execution of multiple character single row functions.
- Concat function : It will concatenate the first value to other value but the operator named || is more powerful than concat function.
Simple Example :
Select Concat(‘Amit’,’S’) from dual;
Output :
AmitS
Difficult Example :
Select Concat(FIRST_NAME,LAST_NAME) As “Full_Name” from HR.Employees;
The output will be the first name followed by last name from Employees table.
2.Substring Function : If user want to fetch the part of the string from specified column we can use substring function.
Syntax : Substring(String_or_Column_name,Start_Position,End_Position)
Simple example :
Select Substr(‘Amit’,1,3) from dual;
The output will be
Ami
Difficult example:
Select Substr(‘Amit’,-2) from dual;
If the end position is specified with minus operator the output will start from end of the string.The query will give result as ‘it’.
3.Length function : The return value of this function is numeric which will give you the length of the string.
Example :
Select length(‘Amit’) from Dual;
Output :
4.INSTR : It returns the numeric position of the string with specified acron given in the function.
Syntax : Instr(Column_name,start_position,Search_value)
Simple example :
Select INSTR(‘Amit’,’i’ ) from Dual;
The above query will give you position of i which is 3.
Difficult example :
Select INSTR(‘Aereet’,’e’ ,3) from Dual;
The above example will give you position of e after 3rd character. So the output will be 4.
Select INSTR(‘Aereet’,’e’,3 ,2) from Dual;
The oracle server will start searching ‘e’ from position 2 and we require the second ‘e’ after that.
Sot the output will be 5.
5.LPAD and RPAD : If we need to put some special character for the specific length string or number we need to use LPAD and RPAD. LPAD is for padding the values at left side and RPAD is padding values at right side.
Example :
Select RPAD(‘ami’,5,’*’) from Dual;
Output :
Ami**
Select LPAD(‘ami’,5,’*’) from Dual;
Output :
** Ami
6.Replace function : It will replace some character or number with other value.
Example :
Select REPLACE(‘amit’,’i’,’*’) from Dual;
Output :
Am*t
6.Trim : To remove the spaces or letters from leading or trailing we can use the Trim function.
Example :
Select Trim(‘ ‘ from ‘ Amit s ‘) from Dual;
It will remove spaces from beginning and end and output will be
Amit s
Select Trim(LEADING ‘A’ from ‘ Amit s ‘) from Dual;
You can able to remove characters as well. So the output will be ‘mit s’
You can use LEADING and TRAILING statements in the query to remove the spaces or characters as beginning or end.
Scenario 3 : Single row numeric functions
- Round : The function used to round off the value of specified number. If rounding is more than .5 it will add 1 to the return value.
Example :
Select round(10.5) from Dual;
Output :
11
Select round(110.49) from Dual;
Output :
110
Difficult example : We can also use minus arguments.
Select round(110.49,-2) from Dual;
The output of above query is 100.
- Trunc : The Trunc function is used to truncate the value of the number.
Example :
Select trunc(110.49,1) from Dual;
110.4
Output :
Select trunc(110.49,-2) from Dual;
Output :
100
3.Mod function : It will return reminder of the division.
Example :
Select mod(11,2) from dual;
Output :
1
Scenario 4 : Multiple examples of date functions
- Sysdate : It will return current date and time.
Select sysdate from dual;
The output is the current date and time.
2.Months_between : The months_between takes input as 2 dates and gives months between two dates.
Select months_between(’11-oct-20′,’11-oct-04′) from Dual;
The answer is 192 months.
3.Add_months : This function used to add months in specific date.
Select add_months(’11-oct-20′,4) from Dual;
The above query will give you date as 11-Feb-21.
Select add_months(’11-oct-20′,-4) from Dual;
The above query will give you date as 11-June-20.
4.Next_day : Retreive next day by checking nls_date_language.
Select next_day(‘1-dec-20’,1) from dual;
It will fetch next Sunday date.
Output will be 6-dec-20.
5.Last_day : It will return last day of the month.
Select next_day(‘1-dec-20’,1) from dual;
The query will return output as 31-dec-20
Scenario 5 : NVL,NVL2 function examples.
1.NVL : NVL function is return or replace null value to value given.
example :
Select Employee_id,First_name,NVL(Commission_PCT,0) from Employee;
The above query will return the value as 0 for null values of commision_pct column.
2.NVL2 : The NVL2 is another simple function to handle null values but it has 3 arguments.
NVL2(Column_name,Column_name/Value retreive when not null,Column_name/Value retreive when null)
We will take same example for Employee table. If Employee table commission_pct column is not null then employee will get commision+salary else only salary.
Select Employee_id,First_name,Last_name,NVL(commision_pct,’Commision and Salary’,’Only Salary’) from Employees;
3.nullif :
The nullif function will compare 2 expressions if expression1=expression2 then it will return null else it will return expression or value of column. To check this we will compare directly values of 2 strings.
Select nullif(‘amit’,’amit’) from dual;
The above query will return the null value.
Select nullif(‘Amit’,’amit’) from dual;
The above query will return the expression1 which is ‘Amit’.
4.Coalesce : The function will return first not null value. The nested nvl is equal to coalesce.
select Coalesce(null,null,’Amit’),Coalesce(null,’Amit2′,null) from dual;
In above query if you see first coalesce will give you output as Amit and second function will give you first not null value as Amit2.
These are some most important single row functions with real life examples.