Introduction to the two functions in Oracle Functions

In the actual application of Oracle functions, we often use the following two functions, this includes the character manipulation function CONCAT, and the actual application and features of the character manipulation function CONCAT in this article.

Character manipulation functions ------> CONCAT

 
 

  
  
select empno,concat(ename,job) from scott.emp;<=>select ename||job from scott.emp;  
  
  
EMPNO CONCAT(ENAME,JOB)  
  
  
7369 SMITHCLERK  
  
  
7499 ALLENSALESMAN  
  
  
/LENGTH  
  
  
select empno,ename,length(ename) from scott.emp;  
  
  
EMPNO ENAME LENGTH(ENAME)  
  
  
7369 SMITH 5  
 
 

2. select length ('classices') from dual;

LENGTH ('class ')

3

 
 

  
  
/SUBSTR  
  
  
select empno,ename,substr(ename,1,4) from scott.emp;  
  
  
EMPNO ENAME SUBSTR(E  
  
  
7369 SMITH SMIT  
  
  
7499 ALLEN ALLE 
 
 

/INSTR return position value

 
 

  
  
SQL> select ename,instr(ename,'S') from scott.emp;  
  
  
ENAME INSTR(ENAME,'S')  
  
  
SMITH 1  
  
  
ALLEN 0  
  
  
WARD 0  
  
  
JONES 5  
 
 

/LPAD displays the string in some Mode

 
 

  
  
SQL> select ename,lpad(ename,10,'*') from scott.emp;  
  
  
ENAME LPAD(ENAME,10,'*')  
  
  
SMITH *****SMITH  
  
  
ALLEN *****ALLEN  
  
  
WARD ******WARD  
  
  
JONES *****JONES  
 
 

In Oracle functions, numeric functions: ROUND is the most widely used function.

 
 

  
  
SELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1)  
  
  
FROM DUAL;  
 
 

Result:

In order: 45.92 46 50

 
 

  
  
/TRUNC  
  
  
SELECT TRUNC(45.923,2),TRUNC(45.923,0),TRUNC(45.923,-1)  
  
  
FROM DUAL;  
  
  
 
 
 

Result:

In order: 45.92 45 40

/MOD

The above content is an introduction to several common Oracle functions. I hope you will find some gains.