Oracle Basic functions

1) String function---length () function

Used to return string lengths

Select T.name,length (t.name) from Tb_person t

2) left complement string---lpad () function

The Lpad () function is used to complement a full string to the left, primarily for the formatting of strings.

Format: Formats a string as a specified length, if there are insufficient parts, and fills a specific character at the left end of the string.

Select Lpad (id,4, ' 0 ') from Tb_person

3) Right complement full string--rpad () function

Used to return a string formatted as a specific number of digits

Select Rpad (id,4, ' 0 ') from Tb_person

4) Connection string---concat () function

Concatenation of two strings

Select concat (' Hello ', ' word ') from dual;

5) Get the lowercase---LOWER () function of the string

Select lower (name) from Tb_person

6) Intercept the string--substr () function

function to intercept the string and return the truncated new string

Select (name,1,3) from Tb_person

7) Capitalize the first character of the word--initcap () function

Select Initcap (name) from Tb_person

8) Invert string---reverse () function

Select reverse (' ABCDEF ') from DUAL;

9) Replace string---replace () function

Used to replace the specified string in a string

Select replace (name, ' n ', 8) from Tb_person//replace the data with N in the field with column name name as 8

10) format string---to_number () function

Converting a numeric string to a numeric type

Select To_number (' 123.123 ') +2 from DUAL;

11) Numeric function

Get the absolute value of a number---ABS () function

Select ABS ( -100) from dual results: 100

12) modulo Operation---MOD () function

Effect: Returns the remainder of a division expression containing two parameters: divisor and divisor

MOD (x, y)

SELECT MOD (2000,6000) from DUAL

13) rounding up--ceil () function

The Ceil () function returns the smallest integer value equal to the numeric parameter.

Select Ceil ( -1.3) from dual results: 1

14) down rounding--floor () function

Returns the maximum integer value less than or equal to the parameter value

Select Floor ( -1.6) from DUAL results: 2

15) Rounding---round function

Returns the rounded value of a value

ROUND (X,[y])

Select ROUND (4.38,1) from DUAL results: 4.4

Select ROUND (4.38) from DUAL results: 4

16) exponentiation---POWER () function

Used to perform a exponentiation, containing two parameters: the base and exponent of the exponentiation operation

POWER (x, y)

SELECT Power (2,3) from DUAL represents 2 of 3 power results 8

17) Calculate the square root--sqrt () function of a number

Used to return the square root of a numeric parameter

Select SQRT (4) from DUAL results: 2

18) formatted numeric--to_char () function

Used to format the data of a numeric type and return the formatted string

To_char (X,[format])

x indicates the original value format is an optional parameter

(1) format character "0"

0 represents a digit digit. Force 0 to be added when the original value does not have a number in the value match

Select To_char (15.66, ' 000.000 ') from dual; Results: 015.660

(2) format character "9"

9 represents a digit bit, and does not fill any characters when the integer part of the original value does not match the digits

Select To_char (15.66, ' 999.999 ') from dual results: 15.660

However, for decimals less than 1, all format characters use 9, and the return value is often not ideal

Select To_char (0.12, ' 999.999 ') from dual results:. 120

Oracle Basic functions