ORACLE string operations

1 string connection SQL> select 'abc' | 'def 'from dual; 'abc' |
------
Abcdef2 lower case
SQL> select lower ('abc012 ');
Lower
--------
Abc012

3 capital select upper ('abc012 ');

Upper
--------
ABC012

4. Left completion

Select lpad ('abc', 5, '0 ');

Lpad
-------
00abc

Select lpad ('abc', 5, '012 ');

Lpad
-------
When 3rd parameters of 01abc are null, the default value is space select lpad ('abc', 5 );

Lpad
-------
Abc

Select lpad ('abc', 5 ,'');

Lpad
-------
Abc 5 right completion select rpad ('abc', 5, '0 ');

Rpad
-------
Abc00

Select rpad ('abc', 5, '012 ');

Rpad
-------
Abc01 6 left blank Delete select ltrim ('abc ');

Ltrim
-------
Abc
7. Right-blank: delete select ltrim ('abc ');

Ltrim
---------------
Select trim ('abc') is deleted blank around abc 8 ');

Btrim
-------
Abc

9 string replacement SQL> SELECT TRANSLATE ('ababab', 'A', '1') FROM DUAL; TRANSL
------
1b1b1b 10 obtain the sub-string SQL> select substr ('abc012', 3, 2) from dual; SU
--
C0 11 String length SQL> select LENGTH ('abc012') from dual; length ('abc012 ')
----------------
612 numeric conversion to string SQL> select to_char (123456, '20140901') from dual; TO_CHAR (999,999,999,999 ,'
----------------
123,456 13 convert a string to a numeric SQL> select to_number ('20160301' | '20160301', '20160301') from dual; TO_NUMBER ('20160301' | '20160301 ', '123 ')
--------------------------------------
123456SQL> select to_number ('123456', '123456') from dual; TO_NUMBER ('123456', '123456 ')
--------------------------------------
123456

#####################################
# Common character and string processing functions
#####################################

# LOWER Function
Purpose: convert a string into lowercase letters.
Example:
Select firstname, lastname from MERs
Where LOWER (lastname) = 'Nelson ';
# UPPER Function
Purpose: convert a string into uppercase letters.
Example:
Select firstname, lastname from MERs
Where UPPER (lastname) = 'Nelson ';

# INITCAP Function
Purpose: convert a string to a mix of uppercase and lowercase letters. Each word starts with an uppercase letter,
Convert other characters into lowercase letters.
Example:
Select firstname, lastname from MERs
Where UPPER (lastname) = 'Nelson ';

Select initcap (firstname), initcap (lastname) from MERs MERS;

# SUBSTR Function
Purpose: Obtain the substring function.
Format: SUBSTR (C, P, L) Where: C indicates the string, P indicates the position of the starting string to be extracted,
L indicates the extracted length. If P is a negative value, it indicates
The last digit is taken backward.
Example:
Select distinct substr (zip, 1, 3)
From MERs MERS;

Select distinct substr (zip, 1, 3), substr (zip,-3, 2)
From MERs MERS;

# LENGTH Function
Purpose: determine the length of the string to be analyzed.
Example:
Select destinct length (address)
From MERs MERS;

# LPAD Function
Function: used for string filling. Fill in the area on the left of the string.
Format: LPAD (C, L, S) where: C indicates the string to be filled, L indicates filling the "after" String
The length of the string. "S" indicates the symbol or character used for filling.
Example:
Select firstname, LPAD (firstname, 12 ,'')
From MERs
Where firstname like '% E % ';

# RPAD Functions
Function: used for string filling. Fill in the area to the right of the string.
Format: RPAD (C, L, S) where: C indicates the string to be filled, L indicates filling the "after" String
The length of the string. "S" indicates the symbol or character used for filling.
Example:
Select firstname, RPAD (firstname, 12 ,'')
From MERs
Where firstname like '% E % ';

# LTRIM Function

# RTRIM Functions

# REPLACE Functions
Function: Used to replace strings.
Format: REPLACE (C, S, R) Where: C indicates the string to be processed, S indicates the string to be searched, and R indicates
String to be replaced. Similar to the "search and replace" function.
Example:

# CONCAT Functions
Function: String concatenation function |

 

 

######################################## ############################
# Numeric Functions
######################################## ############################

# ROUND Function
Purpose: Used to round a numeric field to a specified precision.
Format: ROUND (N, P)
Example:
Select title, retail, ROUND (RETAIL, 1), TRUNC (RETAIL, 1)
From books;

# TRUNC Function
Purpose: Used to return the specified precision of the number field.
Format: ROUND (N, P)
Example:
Select title, retail, ROUND (RETAIL, 1), TRUNC (RETAIL, 1)
From books;

 

######################################## ############################
# Date functions
######################################## ############################

# Oracle date functions display date values in DD-MON-YY format, which represents two days, three months abbreviated
And two years.
Example: 20-MAR-02

# MONTHS_BETWEEN Function
Function: indicates the number of days that are different between two months.
Example:
Select title, MONTHS_BETWEEN (orderdate, pubdate)
From books natural join orders natural join orderitems
Where order #=1009;

# ADD_MONTHS Function
Function: indicates the time after a certain date.
Example:
Select title, pubdate, ADD_MONTHS (pubdate, 60) "Drop Date"
From books
Order by "Drop Date ";

# NEXT_DAY Function
Purpose:
Format: NEXT_DAY (d, DAY) Where: d indicates the start date, DAY indicates a DAY of the week to be determined
Example:
Select order #, orderdate, NEXT_DAY (orderdate, 7)
From orders
Where order #=1010;

# TO_DATE Function
Purpose:
Example:
Select order #, orderdate, shipdate
From orders
Where orderdate = to_date ('3-31-2003 ', 'Mm-DD-YYYY ');

##### Date Format element value

Name of all months written by MONTH, with a space added to the total width of 9 characters.
APR
MM Month's double-digit value 04
Month IV of RM roman numerals
D. Value of a day in a week
DD value of a certain day in January
DDD value of a day of a year
The name of a DAY in a week, with a space to reach the width of Wednesday.
WED is short for the three letters of a day in a week.
YYYY displays the four-digit year
YYY, YY, and Y show the last three digits, last two digits, and last digits of the year.
YEAR
B .C. Or A.D.

######################################## ############################
# NVL Function
######################################## ############################
Purpose: The NVL function can be used to solve the problems caused by mathematical operations on fields that may contain NULL values.
In Oracle9i, the NULL value is not equal to space or 0. When NULL is used in calculation, the result is NULL.
The NVL function uses a value to replace the existing NULL value.

Select order #, orderdate, shipdate, shipdate-orderdate "Delay"
From orders;
When performing the preceding query, some columns are blank. Please note!

Select order #, orderdate, NVL (shipdate, to_date ('07-4-03 ', 'dd-MM-YY ')),
NVL (shipdate, to_date ('07-4-03 ', 'dd-MM-YY')-orderdate "Delay"
From orders
Where order #=1018;

If the value of the shipdate column is NULL, use '07-04-03 'to replace the value of shipdate.

 

######################################## ############################
# NVL2 Functions
######################################## ############################

Purpose: The NVL2 function is a variant of the NVL function. It allows different options, depending on whether there is a NULL value.
Format: NVL2 (x, y, z). y indicates the data to be replaced when x is not NULL, and z indicates the number to be replaced when x is NULL.
Data. This makes it more flexible for users to process NULL values.

For example: NVL2 (commission, salary, salary + commission)
If the Commission is NULL, the total salary is the salary. If the Commission is not NULL, the total salary is calculated as the salary plus the Commission.

Select order #, orderdate, NVL2 (shipdate, 'shipped ', 'not Shipped') "Status"
From orders;

 

######################################## ############################
# TO_CHAR Function
######################################## ############################
Purpose: Convert dates and numbers into formatted strings.
Format:

Select title, to_char (pubdate, 'month DD, yyyy') "Publication Date ",
To_char (retail, '$9999.99') "Retail Price"
From books
Where isbn = 0401140733;

######################################## ############################
# DECODE Function
######################################## ############################
Purpose:
Format:

DECODE is an exclusive function provided by Oracle. It is a powerful function. Although it is not the standard of SQL,
But it is very useful for performance. Currently, other database vendors cannot provide DECODE-like functions, and even some
Database vendors criticize Oracle SQL standards. In fact, such criticism is somewhat one-sided or inadequate. Just as some automakers complained about Henry. Ford's carriage is not standard.
In logical programming, If-Then-Else is often used for logical judgment. In DECODE syntax, this is actually the logic processing process. Its syntax is as follows:

DECODE (value, if1, then1, if2, then2, if3, then3,... else)

Value indicates any column of any type in a table or any result obtained by calculation. When each value is tested, if the value is if1, the result of the Decode function is then1; if the value is if2, the result of the Decode function is then2; and so on. In fact, multiple if/then pairs can be provided. If the value result is not equal to any given pairing, the Decode result returns else.
Note that if, then, and else can both be functions or computing expressions.

DECODE implements table transpose

For example, you want to display the JOB content in the following column results:

SQL> select empno, ename, job, sal, deptno from emp
2 order by deptno, job;

EMPNO ENAME JOB SAL DEPTNO
-------------------------------------------------
7934 miller clerk 1300 10
7782 clark manager 2450 10
7839 king president 5000 10
7788 scott analyst 3000 20
7369 smith clerk 800 20
7876 adams clerk 1100 20
7566 jones manager 2975 20
7938 Zhao yuanjie software 12345 20
7698 blake manager 2850 30
7499 allen salesman 1600 30
7654 martin salesman 1250 30
7844 turner salesman 1500 30
7521 ward salesman 1250 30

18 rows selected.

Let's look at the following query results:

SQL> select deptno, job, sum (sal) from emp group by deptno, job;

Deptno job sum (SAL)
-----------------------------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 analytic 3000
20 CLERK 1900
20 MANAGER 2975
20 software 74070
30 MANAGER 2850
30. SALESMAN 5600

9 rows selected.

From the above results, if you want to replace the JOB into a column, you only need to describe the JOB column with DECODE. The created view is as follows:

Create or replace view empv
Select deptno,
Sum (decode (job, 'analyst', sal, 0) ANALYST,
Sum (decode (job, 'cler', sal, 0) CLERK,
Sum (decode (job, 'manager', sal, 0) MANAGER,
Sum (decode (job, 'President ', sal, 0) PRESIDENT,
Sum (decode (job, 'salesman', sal, 0) SALESMAN,
Sum (decode (job, 'soft', sal, 0) Software
From emp group by deptno;

The running sample is as follows:
SQL> create or replace view empv
2 select deptno,
3 sum (decode (job, 'analyst', sal, 0) ANALYST,
4 sum (decode (job, 'cler', sal, 0) CLERK,
5 sum (decode (job, 'manager', sal, 0) MANAGER,
6 sum (decode (job, 'President ', sal, 0) PRESIDENT,
7 sum (decode (job, 'salesman', sal, 0) SALESMAN,
8 sum (decode (job, 'soft', sal, 0) Software
9 from emp group by deptno;

View created.

SQL> select * from empv;

Deptno analyst clermanager president salesman Software
----------------------------------------------------------------------
10 0 1300 2450 5000 0 0
20 3000 1900 2975 0 0 74070
30 0 0 2850 0 5600 0

 

 

 

######################################## ############################
# SOUNDEX Function
######################################## ############################

######################################## ############################
# NESTING Function
######################################## ############################

######################################## ############################
# DUAL table
######################################## ############################

######################################## ############################
# SUM function
######################################## ############################

Select sum (retail-cost) "Total Profit"
From orderitems natural join books
Where order #=1007;

######################################## ############################
# AVG Functions
######################################## ############################

Select avg (retail-cost) "Average Profit"
From books
Where category = 'computer ';

Select to_char (avg (retail-cost), '2014. 99') "Average Profit"
From books
Where category = 'computer ';

######################################## ############################
# COUNT Function
######################################## ############################

Select count (distinct category) from books;

Select distinct count (category) from books;

Select count (*) from orders
Where shipdate is null;

Select count (shipdate) from orders
Where shipdate is null;

When the parameter provided by the COUNT function is a *, all existing records are included. By calculating the entire record, the Count function will not lose the NULL value.

######################################## ############################
# MAX Functions
######################################## ############################

######################################## ############################
# MIN Functions
######################################## ############################

######################################## ############################
# Group by clause
######################################## ############################

Select category, to_char (avg (retail-cost), '2017. 99') Profit
From books
Group by category

Select customer #, order #, sum (quantity * retail) "Order Total"
From orders natural join orderitems natural join books
Group by customer #, order #

######################################## ############################
# HAVING clause
######################################## ############################

Select category, to_char (avg (retail-cost), '2017. 99') Profit
From books
Group by category
Having avg (retail-cost)> 15;

Select category, to_char (avg (retail-cost), '2017. 99') Profit
From books
Where pubdate> to_date ('01-01-02 ', 'dd-MM-YY ')
Group by category
Having avg (retail-cost)> 15;

Group statistics by order, but only show orders with a total amount of more than 100 USD.

Select customer #, order #, sum (quantity * retail) "Order Total"
From orders natural join orderitems natural join books
Group by customer #, order #
Having sum (quantity * retail)> 100;

 

######################################## ############################
# STDDEV Function
######################################## ############################
Purpose:
Calculates the standard deviation of a specified field. The "standard deviation" calculation is used to determine the closeness of a single value in a group of numbers to the average value.
Example:
Select category, avg (retail-cost), stddev (retail-cost)
From books
Group by category;

To interpret the value calculated by the standard deviation, you must compare it with the "average profit" of each type. For example, if you check the Cooking type in the above results, the average profit of the book is USD 8.60. But most books are close to this average, or are most books generating a profit of $1 while a book generating a profit of $20?
The standard deviation is the approximate statistical value of the number of books within a certain range near the average value.

######################################## ############################
# VARIANCE Function
######################################## ############################

Role: used to determine the degree of data Dispersion in a group. The variance of a set of records is based on the maximum value of the specified field
And the minimum value.
If the data values are dense, the variance is very small, but if the data contains extreme values (very large or small)
The variance is very large.
Example:
Select category, variance (retail-cost), min (retail-cost), max (retail-cost)
From books
Group by category;

Explanation of the result:
The above query results list the types of all BOOKS in the BOOKS table, the variance of profits of each category, and the variance of each category.
The minimum profit and the maximum profit in (for comparison ). Like the standard deviation, if a group of data contains only one value, the calculated variance is 0. However, unlike the standard deviation, the variance is not calculated in the same unit as the source data.

To explain the results of the VARRANCE function, you must check how large or small the value is. For example, the Cooking type has a variance smaller than other types. This means that the book profits in the Cooking category are more intensive (that is, the profits are not covered in a larger scope ).
Take a look at the minimum profit and maximum profit of books in the Cooking category. Note that the profit range is USD 2.30,
(9.75.7.45 ). On the other hand, let's look at the Family Life type. This type has the highest variance. If the minimum profit and the maximum profit are compared, the profit range is large.
This warns administrators that some books may produce very small profits. Other products can generate large profits to know the business data of an enterprise.