Common oracle SQL Functions

Functions in oracle are divided into character functions, number functions, date functions, empty processing functions, conversion functions, and other common functions. The following are some SQL functions commonly used in ORACLE: lower () function content conversion LOWER case SQLSELECTLOWER (T_NAME) FROMtest_partitioning2; lower (T_NAME )------------

Functions in oracle are divided into character functions, number functions, date functions, empty processing functions, conversion functions, and other common functions. The following are some SQL functions commonly used in ORACLE: lower () function content conversion lowercase SQL SELECT LOWER (T_NAME) FROM test_partitioning2; LOWER (T_NAME )------------

Functions in oracle include: character functions, numeric functions, date functions, null processing functions, conversion functions, and other common functions.

This time I will mainly sort out several SQL functions commonly used in ORACLE as follows:

The lower () function converts the content to lowercase letters.
SQL> SELECT LOWER (T_NAME) FROM test_partitioning2;
LOWER (T_NAME)
-------------
A
B
C
D
E
E
E
7 rows selected

Replace the upper () function content with uppercase letters
SQL> SELECT UPPER (T_NAME) FROM test_partitioning2;
UPPER (T_NAME)
-------------
A
B
C
D
E
E
E
7 rows selected

Ltrim () removes leading and trailing spaces (two leading and trailing spaces)

SQL> select length (ltrim ('oracle '), length (ltrim ('oracle', '') from dual;
LENGTH (LTRIM ('oracle ') LENGTH (LTRIM ('oracle ',''))
-------------------------------------------------
8
-- The second parameter in ltrim is not specified. By default, the character ''space is deleted, which is the same as that of ltrim ('oracle;

Ltrim deletes the specified character from the string
SQL> select ltrim ('oracle ', 'roc'), ltrim ('lllearner ', 'ale') from dual;
LTRIM ('oracle ', 'roc') LTRIM ('lllearner ', 'ale ')
---------------------------------------------
Acle rner
-- The second parameter of the ltrim function is not deleted by 'roc ', but by a single character. Consecutive characters are deleted consecutively.

Rtrim () removes leading and trailing spaces (two leading and trailing spaces)

SQL> select length (rtrim ('oracle '), length (rtrim ('oracle', '') from dual;
LENGTH (RTRIM ('oracle ') LENGTH (RTRIM ('oracle ',''))
-------------------------------------------------
8
SQL> select rtrim ('oracle ', 'E'), rtrim ('learner', 'rle') from dual;
RTRIM ('oracle ', 'E') RTRIM ('learner', 'rle ')
-----------------------------------------
Oracl learn
-- This function serves the same purpose as the ltrim function, except that it is deleted from the right to the left.

Trim () functions include ltrim () and rtrim () functions.
Syntax: TRIM ([LEADING | TRAILING | BOTH] [trim_char_from] listing or expression)
LEADING: Indicates deletion from the left
TRAILING: delete on the right
BOTH: delete BOTH sides at the same time (default)
Example:
SQL> SELECT TRIM (LEADING '*' FROM '** oracle *') c1, TRIM (TRAILING '*' FROM '** oracle *') c2, TRIM (BOTH '*' FROM '** oracle *') c3,
2 TRIM ('*' FROM '** oracle *') c4, LENGTH (TRIM ('oracle ') c5 from dual
3;
C1 C2 C3 C4 C5
-------------------------------------
Oracle *** oracle 6

Length () and lengthb () Functions
Both return length, the former returns the string length, and the latter returns the length of bytes.
SQL> select length ('database'), lengthb ('database') from dual;
LENGTH ('database') LENGTHB ('database ')
---------------------------------
3 9

Substr () and substrb () Functions

Both are strings of the returned string. The latter returns the string according to the characters.


SQL> select substr ('oracle ', 1, 3) c1, substr ('oracle', 1, 3) c2, substr ('learning database', 1, 3) c3, substrb ('learning database', 1, 3) from dual;
C1 C2 C3 SUBSTRB ('learning database', 1, 3)
----------------------------------------
Ora

Round () function

Function returns the value in the list, in the syntax format.
ROUND (listing, X) X indicates to keep a few decimal places. If this parameter is left blank, the default value is 0. The number of decimal places is rounded to the nearest decimal point.
Example:
SQL> select role (123.456) from dual;
--------------
123
SQL> select role (123.456) from dual;
ROUND (123.456)
--------------
123
-- The round () function can also process date data.

TRUNC () function

Function: truncates the values represented by a column name. The syntax is as follows:
TRUNC (column name, x) x indicates the number of digits to the decimal point (not rounded). The default value is 0. If x is negative, it indicates the number of digits to the left of the decimal point is truncated.
SQL> select trunc (123456.78) from dual;
TRUNC (123456.78)
----------------
123456
SQL> select trunc (123456.78, 1) from dual;
TRUNC (123456.78, 1)
------------------
123456.7

-- The second parameter of trunc is a negative number, which indicates truncation from the left of the decimal point. The number of truncated digits is automatically supplemented by 0. As follows:
SQL> select trunc (1234.567,-1) A, trunc (123456.789,-2) B, trunc (3456332.1,-4) C from dual;
A B C
------------------------------
1230 123400 3450000
The -- trunc function can also process date data.
-- Trunc is different from round. round is rounded to the decimal point, while trunc is truncated directly. This is the difference between the two functions.

Last_day () function

Function: obtains the last day of the month of the current date.
SQL> select sysdate, last_day (sysdate) last_day from dual;
SYSDATE LAST_DAY
----------------------
2013/10/20 2013/10/31

NVL () function

Function: converts null values. determines whether the values of an expression are null and returns the corresponding list or expression. It is mainly used to process null values. The syntax is as follows:
NVL (column name or expression, column name or expression)
If the first parameter value returns a null value, the second parameter value is returned. Otherwise, the first parameter value is returned. The first and second parameter values can be of any type, but the two parameters must be of the same type.

NVL2 () function NVL () function extension function

Function is also a null value conversion function. If the first parameter value is not empty, the second parameter value is returned. Otherwise, the third parameter value is returned. The first parameter can be of any type,

Second, the third parameter is of any type except the LONG type. Syntax:
NVL2 (column name or expression, column name or expression, column name or expression)

TO_CHAR () function

Function: convert non-character data to character data and set the output format of the character. The syntax is as follows:
TO_CHAR (column name or expression, [format], [NLS parameter])
The first parameter: Specifies the conversion column name.
The second parameter specifies the conversion format for the conversion column name.
Third parameter: displays the supported parameters of the format country language. If this parameter is not set, use the default NLS parameter to specify
SQL> SELECT TO_CHAR (1234567, '000000') C1, TO_CHAR (9,999,999, '0000009') C2, TO_CHAR (123.456, '9. 99') C5 from dual;
C1 C2 C5
-------------------------
1,234,567 123 #####

#####: When the length of the integer part is greater than the length specified in the format string, a string consisting of # is returned.

SQL> select to_char (systimestamp, 'yyyy-MM-DD hh24: MI: ss') d1, to_char (systimestamp, 'ww ') d2, to_char (sysdate, 'w ') d3 from dual;
D1 D2 D3
-----------------------
20:56:58 42 3

TO_DATE (Function

Function: convert data of the sequence type to the date type and set the output format of the date. The syntax is as follows:
TO_CHAR (column name or expression, [format]), [NLS parameter]
First parameter: Convert column name
Second parameter: Specify the display format after conversion
Third parameter: displays the supported parameters of the format country language. If this parameter is not set, use the default NLS parameter to specify
SQL> select to_char (to_date ('1970-10-20 ', 'yyyy-mm-dd'), 'day') from dual;
TO_CHAR (TO_DATE ('2017-10-20 ','
------------------------------
Sunday

DECODE () function
Function: similar to the if else branch statement in advanced languages
SELECT T_ID, T_NAME, T_RESEARCH, DECODE (T_TITLEID, 1, 'Professor ', 2, 'associate Professor', 3, 'others') from dual;