Inbuilt Functions
SQL inbuilt functions are used to perform the operations on the columns of the table and returning a single value at the end in the output.Firstly, I have described about the dual table in oracle .
SQL dual table : Dual is the special table which is already present in the oracle. Dual is small but useful oracle table created for testing functions or doing quick calculations. it is having one row and one column and it does not contain any data.
Example
select 2*5 from dual;
Output: 10
Now I have explained inbuilt functions of SQL with examples.
Following are in built functions.
- Lower() : This function is used to convert the capital letters of the string and characters into the small one.
Example
select lower('AKASH')from dual;
Output: akash
- Upper() : This function is used to perform the reverse process and convert the lower word into the capital one.
Example
select upper('akash')from dual;
Output: AKASH
- CONCATENATION(||) : This function is used to concatenate the two or more columns of a table.
Example : Let if the employee table has two column first_name having value "Ram" and last_name having value "kumar" then the result according to the query is as follows.
select first_name || last_name from employee; In this query both the column concatenate to each other without any space.
Output: Ramkumar
select first_name || ' ' || last_name from employee; In this query both the column concatenate to each other with a space in between them.
Output: Ram kumar
select first_name ||,|| last_name from employee; In this query both the column concatenate to each other after add a comma in between them.
Output: Ram,kumar
- Length() : This function is used to find the length of the value stored in the column like
Example
select length('Akash') from dual;
Output: 5
- INITCAP() : This function is used to capitalize the first character of word(each word).
Example
select initcap('akash')from dual;
Output: Akash
select initcap('hello how are you') from dual;
Output: Hello How Are You
- LPAD( ) : This function is used to add some extra character in the column value in the left side.
Syntax
lpad(string,length,ch);
where string is any string and length is the total length of the string and ch is the any character which will padded on the left side.
Example
select lpad('hello',10,'*') from dual;
Output: *****hello
- RPAD( ) : This function is used to add some extra character in the column value in the right side.
Syntax
rpad(string,length,ch);
Where string is any string and length is the total length of the string and ch is the any character which will padded on the right side.
Example
select rpad('hello',10,'*') from dual;
Output: hello*****
- LTRIM( ) : This function helps us to trim off unwanted characters from left side of the specified string if any specified characters are not mentioned then space is removed.
Syntax
ltrim(string ,'set');
Example
select ltrim('AKASH','A') from dual;
Output: KASH
- RTRIM( ) : This function helps us to trim off unwanted characters from right side of the specified string if any specified characters are not mentioned then space is removed.
Syntax
rtrim(string ,'set');
Example
select rtrim('AKASH','SH') from dual;
Output: AKA
gSubstr() :This function helps us to print specific number of characters from a specific position of the string.
Syntax
substr(string,n,m);
Where n is the starting position and m is the number of characters
Example
select substr('Welcome',2,4) from dual;
Output: elco
We can also select the characters from the starting and the ending part of the string as follows
Example
select substr('welcome',-3);
Output: ome
This statement will display last three characters of the string.
11. ASCII() : This function gives the ASCII value of the first character of the string.
Example
select ascii('a') from dual;
Output: 97
Example
select ascii('ABC')form dual;
Output: 65
12. Chr() : This function gives the character form of a number.
Example
select chr(65) from dual;
Output: A
13.Translate() : This function is used to translate the specific character of a string into other character.
Syntax
translate (string,'set1','set2'); Where set2 will replace the character of set1 in string
Example
select translate('mumbai','mu','zt')from dual; In this each m is replaced by z and u replaced by t.
Output: ztzbai