Common Oracle Functions

1,Ceil (N)Function: Take a value greater than or equal to a value.NThe smallest integer

 

2,Floor (N)Function: Take a value less than or equalNThe maximum integer

 

3,InstrAndLimit B: Query the position of the target string in the source string,InstrIs in character format,Limit BByte mode

The format of the instr method is
Instr (source string, target string, start position, matching serial number)
For example, in instr ('upgrade floor ', 'or', 3, 2), the source string is 'upgrade floor', the target string is 'or', And the start position is 3, the position of the 2nd matching items is 14.
The default search order is left to right. When the start position is a negative number, search from the right side, but the position is calculated from left to right.
So

Select instr ('upgrade floor ',' or ',-1, 1) "instring" from dual:
Instring
------
14

 

4,LengthAndLengthb: Evaluate the length of the stringLengthIt calculates the length of characters,LengthbThe length of the byte is calculated.

 

5,SubstrAndSubstrb:Returns the string with the specified start position and length.SubstrTruncate by character,SubstrbIntercept by byte

Format: substr (string, start_position, [length])
Start_position: When the value is positive, it starts from the left.

When it is a negative number, it starts from the right.

[Length]: an optional parameter. If this parameter is required, it indicates that the value ranges from start_position to the end of the string.

If length has a value, it is a string with the length from start_position to the right.

Example:

Substr ('this is a test', 6, 2) wowould return 'is'
Substr ('this is a test', 6) wocould return 'is a Test'
Substr ('techonthenet ', 1, 4) wocould return 'tech'
Substr ('techonthenet ',-3) wocould return 'net'

Substr ('techonthenet ',-3, 3) wocould return 'net'
Substr ('techonthenet ',-6, 3) wocould return 'the'
Substr ('techonthenet ',-8, 2) wocould return 'on'