Description of related functions in Oracle

  3, ROUND function ROUND (number, num_digits) rounds numbers to only num_digits decimal places. 4, Lpad, Rpad function Lpad () function: The Lpad function fills the left string with some specific characters whose syntax is as follows: Lpad (string,n,[pad_string]) string: But the length of the character or parameter n: character, Is the number of returned strings, if this number is shorter than the length of the original string, the Lpad function will intercept the string into N characters from left to right; Pad_string: is an optional argument, the string is pasted to the left of the string, and if this argument is not written, the Lpad function will paste a space on the left side of the string. For example: Lpad (' Tech ', 7); will return to ' Tech ' lpad (' tech ', 2); will return ' Te ' Lpad (' tech ', 8, ' 0 '); will return ' 0000tech ' Lpad (' Tech on the net ', ' Z '); Will return ' Tech on ' net ' Lpad (' Tech on ' net ', ' Z '); will return the use of the ' Ztech on ' Rpad () function: The Rpad function fills the string on the right with some specific characters in the following syntax: Rpad (string,n,[pad_string]) string: But the length of the character or argument N: Character, Is the number of returned strings, if this number is shorter than the length of the original string, the Lpad function will intercept the string into N characters from left to right; Pad_string: is an optional argument, the string is pasted to the right of the string, and if this argument is not written, the Lpad function will paste a space on the right side of the string. For example: Rpad (' Tech ', 7); will return to ' Tech ' rpad (' tech ', 2); will return ' Te ' rpad (' tech ', 8, ' 0 '); will return ' tech0000 ' Rpad (' Tech on the net ', ' Z '); Will return ' Tech on ' net ' Rpad (' Tech on ' net ', ' Z '); will return the ' tech on the Netz ' 5, LTrim, RTrim function LTrim (x,y) function is to truncate the character in X by one of the characters in Y, and to start from the leftLine, as long as you encounter some characters in Y, the characters in X are truncated until the function command ends when the character in X is encountered with characters that are not in Y. The function takes 109 as a three-character 1,0,9 at the beginning of the string, and 1,0,9 any one of these three characters.

SELECT LTRIM (' 1092002081100058424 ', ' 109 ') from dual UNION All SELECT LTRIM (' 1091000000002671251 ', ' 109 ') from dual UNION All SELECT LTRIM (' 1000000002671251 ', ' 1 ') from dual UNION All SELECT LTRIM (' 1000000002671251 ', ' ten ') from dual The results are as follows: 1 2002081100058424 2 2671251 3 000000002671251 4 2671251

RTRIM ( , ) means:

First find any character in ' C2 ' from the right side of the string ' C1 ', this example is ' w ', ' Q ' until the ' wfrqqww ' right side is not ' w ' and ' Q ' characters

The data queried by SELECT RTRIM (' wfrqqww ', ' QQ ') from dual is WFR,

6. The InStr InStr function returns the position of the string to be intercepted in the source string. The syntax is as follows:

InStr (string1, string2, start_position,nth_appearance) [1] [2]

String1	The source string to find in this string.
string2	The string to find in the string1.
Start_position	Represents which location of the string1 to start the lookup. This parameter is optional, if omitted defaults to 1. The string index starts at 1. If this parameter is positive, retrieves from left to right, if this parameter is negative, retrieves from right to left, and returns the starting index of the string to find in the source string.
Nth_appearance	Represents the string2 to find the first occurrence. This parameter is optional, and if omitted, the default is 1. If the system is negative, the error occurs.

Attention:

The location index number starts at 1.

If String2 is not found in String1, the InStr function returns 0.

Example:

SELECT InStr (' Syranmo ', ' s ') from dual; --Return 1

SELECT InStr (' Syranmo ', ' RA ') from dual; --Return 3

SELECT InStr (' Syran Mo ', ' a ', 1,2) from dual; --Return 0 7, substr

SUBSTR (string, intercept start position, intercept length)//return the intercepted word

substr (' Hello World ', 0, 1)//return result is ' H ' * to intercept a string with a length of 1 starting from the first character of the string

substr (' Hello World ', 1, 1)//Returns the result is ' H ' *0 and 1 are all representations of the start position of the intercept as the first character

substr (' Hello world ', 2,4)//return result to ' Ello '

substr (' Hello world ', -3,3)//Return result is ' rld ' * negative number (-i) indicates the beginning of the intercept is the right end of the string to the left of the first character