Oracle common functions-characters and math functions

in the Oracle In the development and use, often need to use a variety of functions, this chapter summed up the simple string, mathematical functions, the need to use it later also convenient point, but also to make the students less go a little detour.

-- characters commonly used character correlation function

1 , substr string Interception

substr ( string , intercept start position , intercept length )

Select substr (' abcdef ', 1,3) from dual

intercept the start position, 0 and the 1 is the first character that represents the beginning of the Intercept;

2 , InStr Find substring location

Select InStr (' Abcfdgfdhd ', ' FD ') from dual

Returns the position of the first substring.

3 , || String Connection

Select ' Hello ' | | ', world ' fromdual;

4 , Trim handling on both sides of a string

Trim It is generally used to delete spaces on either side of the string, or to delete the specified characters on either side of the string. Trim Specifies that the deleted string can only be a single character.

1. Trim () Remove the half-width spaces on both sides of the string.

2. LTrim () Remove the half-width space to the left of the string.

3. RTrim () Remove the half-width space to the right of the string.

4. Trim (' character 1 ' from ' string 2 ') starts from Both sides of the character 2 string, removing the specified character 1.

5. Trim ([leading | trailing | both] Trim_char from string) removes the specified character from string Trim_char.

Leading: deletes from the beginning of the string.

Trailing: deletes from the end of the string.

both: removed from both sides of the string.

Select Trim (' FFM ') as name from dual;

Select LTrim (' FFM ') as name from dual;

Select RTrim (' FFM ') as name from dual;

Select trim (Leading ' f ' from ' FFMMFF ') from dual;

Select trim (trailing ' f ' from ' FFMMFF ') from dual;

Select Trim (both ' f ' from ' FFMMFF ') from dual;

5 , ASCII returns the first letter of the string Ascii value

Select ASCII (' a ') from dual

6 , CHR return ASCII the letter that corresponds to the value

Select CHR from dual

7 , length Calculating string Lengths

Select Length (' FFM ') from dual

8 , Initcap (capitalize the first letter) , Lower ( lowercase ), Upper ( variable capitalization )

Select lower (' FFM '), Upper (' FFM '), Initcap (' FFM ') from dual;

9,replace Replace character

Replace ( ' the string that will be changed ', ' the string that was replaced ', ' Replace string ' )

Select replace (' FFM ', ' m ', ' 1983 ') from dual;

Ten , Translate replace the specified string

TRANSLATE (STRING,FROM_STR,TO_STR)

TRANSLATEis aREPLACEa superset of the functionality provided. Return will (all occurrences of)From_strreplace each character in theTo_strafter the corresponding character in thestring. IfFrom_strthanTo_strlong, then inFrom_strand not inTo_strthe extra characters in thestringare removed because they do not have the corresponding substitution characters. To_strcannot be empty.

Select Translate (' FFM ', ' m ', ' 1983 ') from dual;

The returned result is: ff1

One , Lpad [ left refill ] rpad [ right padding ] ( for controlling output format )

Select Lpad (' FFM ', 8, ' = ') s1, Rpad (' FFM ', 8, ' = ') s2 from dual;

-- commonly used numbers, mathematical correlation functions

1 , take the whole function ( Ceil Rounding up , floor Rounding down)

Select Ceil (66.6) N1,floor (66.6) N2 from dual;

2 , Take power (Power) and the Find square root (sqrt)

Select Power (3,2) n1,sqrt (9) N2from dual;

3 , Seek redundancy (MoD)

Select mod (9,5) from dual;

4 , returns a fixed number of decimal digits (round: rounding,trunc: Direct truncation )

Select Round (66.667,2) N1,trunc (66.667,2) N2 from dual;

5 , the symbol for the return value (sign positive return is 1, negative number is -1)

Select sign ( -32), sign (293) fromdual;

6 , return x the absolute value ABS (x)

SELECT ABS (3), ABS ( -1) from DUAL;

7 , return to x for the bottom y the logarithm LOG (x, y)

8 , return x of the y Second Power POWER (x, y)

9 , return x the square root SQRT (x)

Selectlog (10,100), POWER (3,3), SQRT (4) from DUAL;

Oracle common functions-characters and math functions