Oracle built-in functions

Lpad() function: used to complete the left string.
For example: lpad (‘original data ‘,’ expected length ‘,’ padding character ‘), lpad (‘123’,’5 ‘,’0′) outputs’ 00123 ‘. The code will fill in the string ‘123’ to 5 bits, and will use ‘0’ to fill in the insufficient digits on the left. When the length of the original string is greater than the expected length, lpad (‘123456 ‘,’5’,’0 ‘) will output’ 12345 ‘.

Rpad() function: used to right complete a string.
For example: rpad (‘original data ‘,’ expected length ‘,’ padding character ‘), rpad (‘123’,’5 ‘,’0′) outputs’ 12300 ‘.

Lower() function: used to return the lowercase form of a string.
Example: lower (‘string ‘), lower (‘dsfdfwed’) outputs’ dsfdfwed ‘.

Upper() function: used to return the uppercase form of a string.
Example: upper (‘string ‘), upper (‘dsfdfwed’) outputs’ dsfdfwed ‘.

Initcap() function: used to capitalize the first letter of a string.
For example: initcap (‘string ‘), initcap (‘aaaa’) outputs’ AAAA ‘, initcap (‘eeee’) outputs’ eeee ‘, initcap (‘aaa’)_ BBB ‘) output’ AAA ‘_ BBB ‘, in this case,’ AAA ‘is used_ BBB ‘is used as the parameter of the initcap() function, and the_ Non word characters such as’, ‘/’, ‘?’ are treated as word separators.

Length() function: used to return the length of a string.
Example: length (‘string ‘), length (‘s4df56′) outputs’ 6 ‘.

Subsrt() function: used for string interception.
For example: substr (‘original data ‘,’ start bit ‘,’ end bit ‘), substr (‘1234567’,’3 ‘,’5′) outputs’ 345 ‘, substr (‘234567’,’3 ‘) outputs’ 35647′.

Instr() function: used to get the position of the child string in the parent-child string.
For example: instr (‘parent-child string ‘,’child string’,’What bit to start searching ‘,’How many times to search’), instr (‘kjahflakfe ‘,’a’) outputs’ 3 ‘, instr (‘kjahflakfe’,’a ‘, 1,2) outputs 7.

Ltrim() function: used to remove the white space at the left end of a string.
Example: ltrim (‘string ‘), ltrim (‘aaa’) outputs’ AAA ‘.

The rtrim() function: used to remove the white space at the right end of a string.
Example: rtrim (‘string ‘), rtrim (‘aaa’) outputs’ AAA ‘.

Trim() function: used to delete the first and last spaces of a string
Example: trim (‘string ‘), trim (‘a a a’) outputs’ a a a ‘.

To_ Char() function: used to convert data of other data types to character type.
Example: to_ Char (‘raw data ‘,’ format ‘) to_ Char (‘120 ‘,’99999’) output ‘120’, to_ Char (‘0.96 ‘,’0.00’) output ‘0.96’, to_ Char (‘0.96 ‘,’9.99′) outputs’. 96 ‘,’0’ is used to force the data on the position to be reserved. If there is no corresponding data or the data is 0, then fill in 0 or reserve 0. To_ Char (sysdate, ‘yyyy MM DD’) outputs’ 2020-08-21 ‘.

Chr() function: used to convert ASCII code to string.

Translate() function: used to replace strings.
For example: translate (‘1dfa2asdfsa3 ‘,’abcdefghijklmnopqrstuwwxyz’, ‘) outputs’ 123′.

Abs() function: used to return the absolute value of a numeric parameter.
For example, ABS (- 56) outputs 56.

Round() function: used to return the rounded value of a number.
For example: round (‘original data ‘,’ digits after decimal point ‘), round (‘78.175’) outputs 78, round (‘78.175 ‘, 2) outputs 78.18, and round (‘78.175’, – 1) outputs 80.

Ceil() function: used to round up.
For example: ceil (21.897) outputs 22, ceil (- 21.897) outputs – 21.

Floor() function: used to round down.
For example: floor (21.897) outputs 21, floor (- 21.897) outputs – 22.

Mod() function: used to get the remainder after dividing two numbers.
Example: mod (‘dividend’,’divisor ‘), mod (11,2) outputs 1.

Sign() function: returns the positive and negative of the parameter.
For example: sign (5) outputs 1, sign (- 5) outputs – 1, and the output 0 is equal to 0.

Sqrt() function: returns the square root of a number.
Example: sqrt (4) output 2.

Power() function: to realize the multiplication of numerical value.
For example: power (“base”, “index”), power (4,2) outputs 16.

Trunc() function: used to intercept some numbers.
For example: TRUNC (‘original data ‘,’ digits after decimal point ‘), TRUNC (‘78.175’) outputs 78, TRUNC (‘78.175 ‘, 2) outputs 78.17, TRUNC (‘78.175’, – 1) outputs 70.

Vsize() function: returns the number of bytes of storage space occupied by data.

To_ Convert number () to the function type.
Example: to_ Number (58.260) outputs 58.26.

To_ Date() function: converts a string to a date type.
Example: to_ Date (‘original data ‘,’ format ‘), to_ Date (’08 / 21 / 20 ‘,’mm / DD / yy’) output is 2020-08-21 00:00:00.

add_ Months() function: add a specific month to a date and return a new date.
Example: add_ months(to_ Date (’08 / 21 / 20 ‘,’mm / DD / yy’), 2) output 2020-10-21 00:00:00.

last_ Day() function: returns the last day of the month in which a specific date occurs.
Example: last_ day(to_ Date (’08 / 21 / 20′,’mm / DD / yy ‘)) output 2020-08-31 00:00:00.

months_ Between() function: returns the number of months between two dates.

current_ Date() function: returns the current date.

current_ Timestamp() function: returns the current timestamp.

Extract() function: get a field of date.

Max() function: find the maximum value.

Min() function: find the minimum value.

Avg() function: average.

Sum() function: sum.

Count() function: get the number of records.

Decode() function: multi value judgment.

Nvl() function: handle null values. The function has two parameters. The first parameter is the column to be processed. If the value of the first parameter is null, the value of the second parameter is returned.

Cast() function: cast data type.