How can Oracle functions replace string with lower-case format?

We all know that Oracle functions are widely used in practical applications and are quite familiar with their actual operations and related functions, but do you know how Oracle functions can replace strings with lower-case formats? If you are interested, you can browse the following Article .

Oracle functions:

Lower (char): replace string with lower-case format

Upper (char): replace string with uppercase format

Length (char): returns the length of the string.

Substr (char, m, n): truncates n strings from M.

1) query the EMP table, whose names are displayed in lower case:

 
  
  


   
   
Select lower (ename) from EMP;

  
  
 

2) an employee whose name is five characters long:

 
  
  


   
   
Select * from EMP where length (ename) = 5;

  
  
 

3)

 
  
  


   
   
Select substr (ename, 2, 3) from EMP;

  
  
 

In this way, Smith becomes mit.

4) make the first character of the name in upper case, and other characters in lower case:

 
  
  


   
   
Select upper (substr (ename, 1, 1 ))
| Lower (substr (ename, 2, length (ename)-1) from EMP;

  
  
 

|: This symbol is a connector in Oracle, just like connecting two string types with "+" in Java.

5)

 
  
  


   
   
Select Replace (ename, 'A', 'A') from EMP;

  
  
 

Replace all 'A' in the ename field with 'A'. Of course, this replacement only applies to the display of the query results and will not be replaced in the database.

6) to_date Function

 
  
  


   
   
Insert into myemp values (7903, 'morflame', 'analyst'
, 7566, to_date ('2017-1-1 ', 'yyyy-mm-dd'), 1988, 2345.23, 20 );

  
  
 

The default date format of Oracle is '01-January-1988, so we can use this oracle function to insert data in the date format we like, such as to_date ('2017/1', 'yyyy/MM/dd '), this is also possible.

7) round function: returns the rounded value.

 
  
  


   
   
SQL> select round (SAL), Sal from myemp where ename =

  
  
 

2 'morflame ';

Round (SAL) Sal

2345 2345.23

You can also control the decimal digits:

  
  


   
   
Select round (SAL, 1), Sal from myemp where ename = 'morflame ';

   
   
Round (SAL, 1) Sal

   
   
2345.2 2345.23

  
  
 

8) trunc function: remove the function without rounding.

 
  
  


   
   
SQL> select trunc (Comm, 1), comm from myemp where ename = 'morflame ';

   
   
Trunc (Comm, 1) Comm

   
   
555.5 555.55

  
  
 

The second parameter is-1.

 
  
  


   
   
SQL> select round (Comm,-1), comm from myemp where ename = 'morflame ';

   
   
Round (Comm,-1) Comm

   
   
560 555.55

  
  
 

9) Ceil function: calculates the smallest integer greater than a certain number.

 
  
  


   
   
SQL> select Ceil (Comm), comm from myemp where ename = 'morflame ';

   
   
Ceil (Comm) Comm

   
   
556 555.55

  
  
 

10) floor function: returns the maximum integer smaller than a certain number.

  
  


   
   
SQL> select floor (Comm), comm from myemp where ename = 'morflame ';

   
   
Floor (Comm) Comm

   
   
555 555.55

  
  
 

11) mod

 
  
  


   
   
SQL> select Mod (12, 3) from dual;

  
  
 

-- The dual table here is a virtual table that is only used to test Oracle functions.

MoD (12, 3)

0

12) ABS, returns the absolute value function.

 
  
  


   
   
SQL> select ABS (-3) from dual;

  
  
 

ABS (-3)

The above content is an introduction to Oracle functions, and I hope you will get some benefits.