SQL Function

Function are a very important feature of SQL and can be used to do following :-

  •  Perform calculation on data
  • Modify individual data items
  • Manipulate output for group of rows.
  • Format dates and Number for display.
  • Convert data types
Sql function

Two Types of SQL function

  1. Single Row Function
  2. Character Function

sql-func

Single row function

  • Manipulate data function.
  • Accept arguments and return one values.
  • Act on each row that is returned.
  •  Return one result per row.
  •  May modify the data types.
  •  Can be nested.

It accept argument that can column or expression
single-row-function

Character Function:

It categorized into
1. Case Manipulation Function
2. Character Manipulation Function

1. Case Manipulation Function:

Lower:-  This function is used to convert the given string to lower case
Upper:-  Upper function is used to convert the given string to upper case.
Initcap:- Initcap function is used to convert every first letter of a string to Upper case leaving rest of them in lower case.i.e

Q. List all the employ names in lower case?



Select lower(First_name) from employees;

lower

Q. Convert ORACLE to lower case?




Select lower('ORACLE') from dual;

oracle-lower

For Upper:

Q. Convert all the employ name to Upper case?



 Select upper(First_name) from employees;

upper-case

For Initcap:

A. List all the employees names in Camel case convertion ?


Select initcap(First_name) from employees;

initcap

2. Character Manipulation function

Types of Character manipulation function

  • Concat function.
  • Concatenation Operator.
  •  LENGTH.
  • REPLACE
  • Trim.
  • Substr.
  •  INSTR.

CONCAT function

  • It is used to concatenate (join) two string only
    Ex: (‘Bhavesh, ‘LaKhani’)
    Ans: BhaveshLakhani.

Query: Concating two string


 SELECT concat('Bhavesh','Lakhani') FROM dual;


Result:
concat

Query: Concat first_name and last_name in employees table


SELECT concat(First_name,last_name) FROM employees;

Result:

conacat-name

LENGTH function:

  • It Is used obtain total length of the string.

i.e

Query: Display employee name and length of his name.


SELECT  First_name, LENGTH(First_name)
 FROM     employees

Result:
name-length

Query: Display employee whose name length is 4 character.


SELECT  First_name, LENGTH(First_name)
 FROM     employees
 WHERE  LENGTH(First_name)=3;
Result:
length-function

REPLACE Function

Is used to replace the char in a given string.
Query: Replace O from Oracle with P



SELECT REPLACE ('oracle', 'o','p') FROM dual;

replace-function

TRIM Function

It Is used to delete first char of a given string if it exist.

Query :Removing First character from String


  SELECT trim('o' FROM 'oracle') FROM dual;
 trim

Substr Function

It Returns number of character from string_value starting from the m position.

See below Example to understand Substr

substring
NOTE:

    • substr function return string
    • it will give character only in forward direction.
    • if you specifie only starting index then it will give All character from starting.
      Query:SELECT SUBSTR('Hello World',1,5) FROM dual;
      
      substr1
      
      Query:SELECT SUBSTR('Hello World',7,5) FROM dual;
      
      
      substr2
      SELECT SUBSTR('Hello World',-2,4) FROM dual;
      
      
      substr3
      
      
      SELECT SUBSTR('Hello World',-5,5) FROM dual;
      
      substr4
      
      Query:SELECT SUBSTR('HELLO WORLD',1) FROM DUAL;
      
      substr5
      

      Q: Display employee whose name start from A using substr function?


       SELECT First_name FROM employees WHERE SUBSTR(first_name,1,1)='A';
      
      
      trim

 

INSTR Function

It returns position of a given char in a given String.

Syntax,

INSTR (char to search, string passes, no of occurrence)

it returns  integer number.

query: select instr('Hello World','l',1,2) from dual;

result value: 4
query: select instr('Hello World','l',-1,2) from dual;

result value:4
select instr('Hello World','l',-1,-2) from dual;

result value: ERROR at line 2: ORA-01428: argument '-2' is out of range General Function:

Leave a Reply

Your email address will not be published. Required fields are marked *