SQL built-in function
• ABS
The ABS function returns an absolute value of a value. Negative numbers will go to their negative. Below is its syntax format:
ABS (Number)
Among them, Number is a value that I want to get an absolute value.
The ABS function cuts the value of the value. As shown in the following example:
ABS (5) = 5
ABS (5) = 5
Whether it is 5 or -5, its absolute value is 5.
SQL built-in function
• ACOS
The ACOS function returns a numerical anti-rest value. The results are represented by radians and between 0 to PI.
The syntax format is as follows:
ACOS (Number)
Where Number must be the value between -1 and 1
The following example shows 1 and -1 anti-hout value:
ACOS (1) = 0
ACOS (-1) = 3.1415927 (PI)
SQL built-in function
• add_months
The add_months function plus a specified number of months on a date, so the day in the date will be constant. However, if the start date is the last day of the month, then the result will be adjusted so that the return value still corresponds to the last day of the new January. If, the number of days in the month is less than the number of days in the month, then adjust it back to adapt to the valid date. The syntax format is as follows:
Add_MontHS (Date, Months)
among them:
Date a date value
Months should add the number of months. The number of months to be subtracted
The following example explains the use of the function:
Add_MontHS (to_date ('15-NOV-1961 ',' D-MON-YYYY '), 1) ='
15-DEC-1961
Add_months (to_date ('30-Nov-1961 ',' D-MON-YYYY '), 1) ='
31-DEC-1961
Add_MONTHS (TO_DATE ('31-Jan-1999 ',' D-MON-YYYY '), 1) ='
28-Feb-1999
Note that in the third example above, the function will have to adjust the 31st to 28 days, which has made the result corresponding to the last day of the new January. Because only 28 days in February 1999. In the second example, it is adjusted from 30 to 31, and it is also true for the last day of the corresponding.
SQL built-in function
• ASCII
The ASCII function returns the decimal value of the character of the first letter you point to the character. The return value may be an ASCII code value, or may not be. If the user's database character set is a 7-bit ASCII value, it gives an ASCII code value. The return value is always based on the character set used by the user.
ASCII (String)
Where: String a string. More time is a character
The following example explains the use of the function:
ASCII ('J') = 74
ASCII ('Jeff') = 74
From the second example above, we are not difficult to see, if a multi-string is specified, the function will ignore all characters except the first letter.
SQL built-in function
• Asin
The ASIN function returns a value of an alternate value. The results were represented by radians and between -pi / 2 to Pi / 2.
The syntax format is as follows:
Asin (Number)
Where: Number must be the value between -1 and 1
The following example shows the anti-sinoid values of 1 and -1:
Asin (1) = 1.57
Asin (-1) = - 1.57
SQL built-in function
• Atan
The ATAN function returns an unputective value of a value. The results were represented by radians and between -pi / 2 to Pi / 2.
The syntax format is as follows:
Atan (Number)
Where: Number must be the value between -1 and 1
The following example shows 1 and -1 annectant cut: Atan (1) = 0.7854
Atan (-1) = - 0.7854
SQL built-in function
• Atan2
The Atan2 function returns an interpretation of the two values. The result is expressed in an arc.
The syntax format is as follows:
Atan2 (First, Second)
among them:
First number of first
Second second number
Function Atan2 (First, Second) is the same.
The following example shows the noctitudes of 1 and -1:
Atan2 (1 ,.5) = 1.107
Tan (1 / .5) = 1.107
SQL built-in function
• BFileName
The BFileName function returns a BFile address of a physical file that points to the disk.
The syntax format is as follows:
BFileName (Directory, FileName)
among them:
Directory A directory path string including a specified file. This directory is generated by command CREATE DIRECTORY. But it is not an operating system directory
FileName Specifies the file name of the user attempts to specify in the directory. This file can be a pre-existing file
The following example explains the use of BFileName functions:
BFileName ('gif_dir', 'book_photo.gif')
SQL built-in function
• Ceil
The CEIL function returns a value based on the input value. The input parameter can be non-integer, but the return result is greater than the minimum integer equal to the input parameters.
The syntax format is as follows:
CEIL (NUMBER)
among them:
Number arbitrary value, decimal number
The following example explains the usage of the CEIL function:
CEIL (5.1) = 6
CEIL (-5.1) = - 5
It should be noted that the result of the function is used for negative numbers. The minimum integer greater than or equal to -5.1 is accurate to be -5. This is a mathematical situation, but it is also the feeling of perception in the count.
SQL built-in function
• ChartorowID
The ChartorowID function converts a string into a RowID type.
The syntax format is as follows:
Chartorowid (String)
among them:
String strings to be processed. This string should represent an effective RowID data
The following example explains the usage of the ChartorowID function:
SQL> SELECT ROWID FORM DUAL;
RowID
------------
Aaaadcaabaaaavuaaaaa
SQL> SELECT * FORM DUAL
2 WHERE ROWID = Chartorowid ('Aaaadcaabaaaavuaaaa);
Di
-
X
SQL built-in function
• chr
The CHR function returns a specified value corresponding to the character set by the corresponding database character set. For example, a ASCII value is given, and the character represented by the CHR function is used.
The syntax format is as follows:
CHR (Integer [Using nchar_cs])
among them:
Integer in the corresponding character set, represents the integer type of the corresponding character
Using nchar_cs indicates the corresponding character set
The following example explains the use of the CHR function:
CHR (10) = a Tab Character
CHR (65) = 'a'
These examples assume that based on the ASCII character set
SQL built-in function
• Concat
The Concat function combines two input strings to one and returns the result.
Its syntax format is as follows: Concat (string_1, string_2)
among them:
String_1 first string
String_2 Second string. This string will be stitched in the end of the first string
The following example explains the usage of the Concat function:
Concat ('this is', 'a test') = 'this is a test'
SQL built-in function
• Convert
The Convert function converts a string from one character set to another.
The syntax format is as follows:
Convert (string, dest_char_set [, source_char_set])
among them:
String string to be converted
DEST_CHAR_SET target string
Source_char_set source character set. Omit default database character set
The following example explains the usage of the Convert function:
Convert ('Jonathan', 'WE8EBDIC37C', 'US7ASCII')
Convert ('Jonathan', 'WE8ROMAN8')
The first example is converted from the US7ASCII character set. The second example converts from the default database character set.
SQL built-in function
•
COS
The COS function returns an angle of cosine value. The result is an arc value.
The syntax format is as follows:
COS
(Angle)
among them:
Angle angle value, expressed with an arc
The following example explains the usage of the COS function:
COS
(90 * 3.14 / 180) =. 000796
COS
(0) = 1
SQL built-in function
• Cosh
The COSH function returns a part of the two-dimensional cosine value.
The syntax format is as follows:
Cosh (Angle)
among them:
Angle angle value, expressed in an arc
The following example explains the usage of the COSH function:
Cosh (0) = 1
Cosh (90 * 3.14 / 180) = 2.507
Convert angle values to radian values, you can refer to relevant department information of COS functions
SQL built-in function
• Decode (1)
The DECode function is equivalent to a conditional statement (IF). It compares the input value to the parameter list in the function, returns a corresponding value according to the input value. The parameter list of functions is a number of sequential forms composed of several values and their corresponding result values. Of course, if it is not successfully matched with any of the real sequences, the function also has the default return value. Different from other functions of SQL, the DECode function can also identify and operate null values.
The specific syntax format is as follows:
Decode (Input_Value, Value, Result [, Value, Result ...] [, Default_Result];
among them:
INPUT_VALUE attempts to process the value. The DECode function compared to a series of pre-sequences to determine the last return result.
Value is a numerical value of a set of context. If the input value is successfully matched, the corresponding result will be returned. Corresponding to an empty return value, you can use the keyword null to correspond
Result is a result of a set of context
Default_Result Fails to match any pre-elapse, the default value returned by the function
The following example illustrates how people read the project under the BLOOD_TEST_FLAG in the user Checkup Table Seapark as the command value of the DECODE function.
SELECT Checkup_Type,
Decode (blood_test_flag, 'y', 'yes', 'n', 'no', null, 'none',
'Invalid')
From Checkup;
SQL built-in function
• Decode (2) This SQL statement demonstrates the left and right infrastructure features of the DECODE function. When the input value of the function is the data of the BLOOD_TEST_FLAG column, if the value of the column is 'y', then the function returns 'yes'. If the value of the column is null, then the function returns 'None'. If there is no match with any pre-sequence, it means that the current value is invalid, the function returns 'Invalid'.
In SQL troubleshooting, Decode functions often play a very flexible role. One of the techniques is to convert a list of a table to a list for some purpose. E.g:
SQL> SELECT TO_CHAR (Trunc (birth_date, 'year'), 'YYYY'),
2 count (*)
3 from aquatic_animal
4 where to_char (Trunc (birth_date, 'year'), 'YYYY')
5 in ('1995', '1996', '1997')
6 group by to_char (Trunc (birth_date, 'year'), 'yyyy');
TO_C Count (*)
--- -------
1995 1
1996 3
1997 1
SQL built-in function
This example tells us how many animals have been born in 1995, 1996 and 1997. Each line shows a different year. Once you want to display these values to 3 columns, how to deal with? It can be processed by the method shown in Table B-1.
SQL built-in function
• EMPTY_BLOB
The EMPTY_BLOB function returns an empty BLOB pointer, which can be used in the insert or update command to initialize the BLOB list.
The specific syntax format is as follows:
EMPTY_BLOB ()
This function has no parameters. The following example illustrates how to initialize the BLOB column using this function and insert it into the table as a new row.
INSERT INTO SOME_TABLE
(blob_column)
VALUES (EMPTY_BLOB ());
SQL built-in function
• EMPTY_CLOB
The EMPTY_CLOB function is similar to the functionality of the EMPTY_BLOB function, but it is only corresponding to Clob. It returns an empty Clob pointer, which can be used in the insert or update command to initialize the Clob column.
The specific syntax format is as follows:
EMPTY_CLOB ()
This function has no parameters. The following example illustrates how to initialize the Clob column using this function and insert it into the table as a new line.
INSERT INTO SOME_TABLE
(Clob_Column)
Value (Empty_Clob ());
SQL built-in function
• EXP
The EXP function returns a power of e. In mathematics e often used to represent a designated special value (a decimal unlimited number) 2.178 ..., it is the basis of mathematics. The specific syntax format is as follows:
Exp (Exponent)
among them:
EXPONENT Returns E'm an Exponent Power
Below is the usage of this function:
EXP (1) = 2.7182818
EXP (3) = 20.085537 (2.7182818 * 2.7182818 * 2.7182818)
SQL built-in function
• Floor
The FLOOR function returns a maximum integer smaller than or equal to the number of references. The working mechanism of this function is extremely similar to the case of the function CEIL, but it is just the opposite.
The specific syntax format is as follows:
Floor (Number)
among them:
Number arbitrarily, including decimal numbers
Below is the usage of this function:
Floor (5.1) = 5
Floor (-5.1) = - 6 In order to understand the different mechanisms of the function floor and function CEIL, the function floor (5.1) and function CEIL (-5.1) can be compared.
SQL built-in function
• Greatest
The Greatest function returns the highest value in a value list. Digital data and character data can be processed using the GreateSt function.
The specific syntax format is as follows:
Greatest (Value, Value, Value, ...)
among them:
Value digital data or text data. Generally, all values should be consistent. If a given data type is mixed, the return value will match the first parameter type, and the remaining parameters are naturally converted to the corresponding type.
Below is the usage of this function:
Greatest (1, 3, 9, 45, 93, 2, -100) = 93
Greatest ('Jenny', 'Jeff', 'Ashley') = 'Jenny'
GreateSt ('11', 101) = '11'
The third example reflects the function processing of the parameter type phase. Since the first data type is a string, Oracle converts the number 1 into string '1' to match the corresponding data type. Although the value is 101 greater than 11, if it is converted into a string, the result is just the opposite. In this case, '11 'is greater than' 101 ', and is also the return result of the function.
SQL built-in function
• HEXTORAW
Function HEXTORAW converts a string consisting of hexadecimal characters into a RAW value.
The specific syntax format is as follows:
HEXTORAW (String)
among them:
String hexadecimal string data
SQL built-in function
INITCAP
The InitCap function receives a string and converts all words in the string so that all words begin with uppercase letters.
The syntax format is as follows:
Initcap (String)
among them:
String any VARCHAR2 or CHAR type data
Below is the usage of this function.
INITCAP ('this is a test' = 'this is a test'
SQL built-in function
INSTR
The function INSTR will tell the user, where a string (parameter) is in another string (also parameter).
The specific syntax format is as follows:
Instr (String, Substring [, Start [, Occurrence]])
among them:
String string to be queried
Substring strings are searching
Start Description Start the character location of the search. The default is 1, that is, the search will start from the first character of the string. If, the parameter is negative, the location of the search is calculated from the right, not the default starting from the left.
Occurrence specifies the first few times of the substrings attempting to search, the default value is 1, meant to hope that it will appear for the first time.
This function feeds back an index sequence value, found that the substrings to search are found. The following example pointed out the usage of the INSTR function:
INSTR ('Aaabaaba', 'B') = 4
INSTR ('Aaabaaba', 'B', 1, 2) = 7
SQL built-in function
• INSTRB This function is very similar to the INSTR function, and it will also feed back a string (parameter) in which it is in another string (also parameter). Just the function returns a one-word index position instead of a character index position. In fact, this difference is only meaningful when multiple character sets are used at the same time.
The specific syntax format is as follows:
INSTRB (String, Substring [, Start [, Occurrence]])
among them:
String string to be queried
Substring strings are searching
Start Description Start the character location of the search. The default is 1, that is, the search will start from the first character of the string. If, the parameter is negative indicating that the search is calculated from the right, not the default starting from the left.
Occurrence specifies the first few times of the substrings attempting to search, the default value is 1, which means it will appear for the first time.
This function feeds back an index sequence value, found that the substrings to search are found. The following example points out the usage of the INSTRB function:
INSTRB ('Aaabaaba', 'B') = 4
INSTRB ('Aaabaaba', 'B', 1, 2) = 7
SQL built-in function
• Last_day
Function last_day returns the last day of the specified date of the specified date. The specific syntax format is as follows:
Last_day (date)
among them:
Date a day value
Below is the usage of this function:
Last_day (to_DATE ('29 -Dec-1988 ',' DD-MON-YYYY ')))
= '31 -Dec-1988 '
Last_day (to_date ('1-Feb-2000', 'DD-MON-YYYY')))
= '29 -FEB-2000
SQL built-in function
• Least
The Least function returns the minimum value in the numeric list of parameters. Value data or string data is valid.
The specific syntax format is as follows:
Least (Value, Value, Value, ...)
among them:
Value a digital or text value. It is especially not to note that all data types should be consistent. If the type is mixed, the return value will match the data type of the first parameter, and all other parameters are converted to the data type of the parameter.
Below is the usage of this function:
Least (1, 3, 9, 45, 93, 2, -100) = - 100
Least ('Jenny', 'Jeff', 'Ashley') = 'Ashley'
Least ('110', 12) = '110'
The third example illustrates that the processing mechanism of the function is mixed when the data type of the parameter is mixed. Because the first parameter real string, the number 1 converts the number 1 into string '1', a corresponding data type. Although 110 is greater than 12, if it is converted into a string, the result is just the opposite. In this case, '12 'is greater than' 110 '(a order-based), this is the return result of the function.
SQL built-in function
• Length
The Length function returns the length of the string.
The specific syntax format is as follows:
Length (String)
among them:
String arbitrary string
Below is the usage of this function:
Length ('this is short') = 13LENGTH ('this is a bit luck') = 20
SQL built-in function
• Lengthb
The Lengthb function returns the length of the string. But there is a difference when using the multi-character set.
The specific syntax format is as follows:
Lengthb (String)
among them:
String arbitrary string
Below is the usage of this function:
Lengthb ('this is short') = 13
Lengthb ('this is a bit luck') = 20
SQL built-in function
• LN
The function LN returns a natural logarithm of a value.
The specific syntax format is as follows:
LN (Number)
among them:
NUMBER is more than 0 any value
Below is the usage of this function:
Ln (10) = 2.3025851
EXP (2.3025851) = 10
It can be seen that the result of the function Ln can be obtained by the reuse of the function EXP to obtain the original parameter value of the function Ln by the reproduction function Exp.
SQL built-in function
• log
Log function returns a non-natural logar of numerical values.
The specific syntax format is as follows:
Log (logbase, number)
among them:
Logbase except any value other than 1 and 0, is the bottom of the logarithm
Number and logarithm
Below is the usage of this function:
LOG (10,100) = 2
LOG (Exp (1), 10) = 2.3025851 = ln (10)
Because 102 is equal to 100, the log of 100 is 2 at 10 is 2. Note: log (e, x) = ln (x). You can use exp (1) to get the value of E.
SQL built-in function
• LOWER
Function LOWER Returns the lowercase form of the parameter specifies the string.
The specific syntax format is as follows:
Lower (atring)
among them:
String any varchar2 or char type value
Below is the usage of this function:
LOWER ('this is a test') = 'this is a test'
SQL built-in function
• LPAD
The LPAD function fills the specified string from the left.
The specific syntax format is as follows:
LPAD (String, Numchars [, Padding])
among them:
String any varchar2 or char type string
Numchars Returns the number of characters of strings
Padding fill characters. Is an option, default is a single space
Below is the usage of this function:
LPAD ('Jenny', 10) = 'Jenny' (5 Leading Spaces)
LPAD ('Jenny', 10, '*') = '***** jenny'
LPAD ('Jenny', 10, '*!') = '*! *! * Jenny'
SQL built-in function
• LTRIM
The LTRIM function deletes the prefix character on the left side of the string. General prefix characters are always spaced.
The specific syntax format is as follows:
Ltrim (String [, Trimchars])
among them:
String any varchar2 or char type value
TrimChars The string to be deleted is the usage of this function:
LTRIM ('Jeff') = 'Jeff' (Leading Spaces Removed)
LTRIM ('***** jeff', '*') = 'jeff'
LTRIM ('*! *! * Jeff ",' *! ') =' Jeff '
SQL built-in function
• MOD
The MOD function returns a number of remaining numbers by another.
The specific syntax format is as follows:
MOD (Number, Divisor)
among them:
Number arbitrary value
Divisor any value. This function calculates the remainder of Number / Divisor
Below is the usage of this function:
Mod (14, 12) = 2 (1400 hours = 2: 00 O'Clock)
MOD (10, 10) = 0
MOD (10, 0) = 10
Note: If the divisor is 0, it returns the original number.
SQL built-in function
• Months_between
The MONTHS_BETWEEN function returns the number of months between two dates
The specific syntax format is as follows:
MONTHS_BETWEEN (DATE_1, DATE_2)
among them:
Date_1 Data Data
Date_2 a day data
If the two parameters represent the same date in a certain month, or the function returns an integer value for the last day of the one-month metro. Otherwise, a score will be returned. In addition, if the date represents the number of the first parameter is less than the date representing the second parameter, a negative value is returned.
Below is the usage of this function:
MONTHS_BETWEEN (To_DATE ('29-Dec-1999 ',' DD-MON-YYYY '),
TO_DATE ('29-Dec-1988 ',' DD-MON-YYYY ') = 132
MONTHS_BETWEEN (To_DATE ('29-Dec-1999 ',' DD-MON-YYYY '),
TO_DATE ('24-Nov-1988 ',' DD-MON-YYYY ')) = 133.16129
In the first example, both parameters are No. 29 in one month, so returning a full value (number of different months of difference), in the second example, due to the date of the two parameters is not the same The day, so return a score (based on 31 days, based on 31 days)
SQL built-in function
• new_time
The New_Time function will convert date / time data in time intervals.
The specific syntax format is as follows:
New_time (date, oldzone, newzone)
among them:
Date a day value. In Oracle, except for themselves, all Japanese periods have time and ingredients such as the mark.
Oldzone represents the string of the time zone. The effective time zone is shown in the table. The value of the calculated date is in the time zone.
NewZone is also a string representing the time zone. Date value from the old time zone to the new time zone
Below is the usage of this function:
New_time (to_date ('25 -Dec-1999 8:00 ',' DD-MOM-YYYY HH: MI '),' EST ',' PST ') =' 25-DEC-1999 05:00 '
SQL built-in function
• Next_day
The next_day function returns from the active date of the designation, followed by the date corresponding to the week. The specific syntax format is as follows:
Next_day (date, weekday)
among them:
Date a date data
Weekday a string, indicating a name of the day of the day. You can use full name, you can also use abbreviations, but you must be a valid date for date settings made by the user's programming language.
Below is the usage of this function:
Next_day (to_date ('24-nov-1999 '),' DD-MON-YYYY ',' Friday '
= '26-NOV-1999 '
Next_day (to_date ('24-nov-1999), 'DD-MON-YYYY', 'WED')
= '01 -Dec-1999 '
Note: In the second example, 24-NOV-1999 is exactly on Wednesday: Wednesday. The next 'WED' is naturally 1-DEC-1999, so the result of the function next-day returns is
'01 -Dec-1999 '
SQL built-in function
• NVL
The NVL function requires two parameters. If the first parameter is empty, the second parameter is returned. The second parameter represents the replacement object of the first parameter.
The specific syntax format is as follows:
NVL (Value, Alternative)
among them:
Value is a value that can be empty. It will be used as a return value when it is empty
Alternative as long as it is not empty, the value will be returned
Below is the case where some columns of this function processing the database include null values:
SELECT EMP_ID, NVL (EMP_NAME, 'Name Missing!')
From EMP_TABLE;
In this example, if the employee has a name, it will return its name through this function. If the name of the employee is empty, return 'Name Missing!'
SQL built-in function
• Power
The function Power returns a value of a value.
The specific syntax format is as follows:
Power (Number, Power)
among them:
Number arbitrary value
Power power number. The result of the function is actually: NUMBER Power. If the first parameter is a negative number, the power of the power will be an integer. Other cases can be any value
Below is the usage of this function:
Power (10, 2) = 100 (10 * 10)
Power (10, 3) = 1000
Power (-10, 3) = - 1000
SQL built-in function
RawtoHex
The RawTohex function converts a RAW into a string value indicated by hexadecimal characters.
The specific syntax format is as follows:
Rawtohex (Raw_Value)
among them:
Raw_Value a RAW type value
Below is the usage of this function:
SQL> CREATE TABLE XX (Y Raw (10));
Table created.
SQL> INSERT INTO XX VALUES (HEXTORAW ('414243'));
1 row created.
SQL> SELECT RAWTOHEX (Y) Form xx;
Rawtohex (Y)
--------
414243
SQL built-in function
• Replace
The REPLACE function searches for a specified substring in a string and replaces it into another specified string.
The specific syntax format is as follows:
Replace (String, Substring [, Replace_String])
among them:
String is searched for strings. Search to replace the string in it
Substring is searched for substrs. All found substrings are replaced
Replace_string replaces the result substring. It is an option. If the parameter is ignored, all the substrings that are searched are substantially deleted.
Below is the usage of this function:
Replace ('this is a test', 'is', 'was') = 'Thwas Was A Test'
Replace ('this is a test', 'is') = 'TH A TEST'
SQL built-in function
• Round (for Dates)
The Round function will be processed (rounded) into the form specified by the second parameter.
The specific syntax format is as follows:
Round (Date [, FMT])
among them:
Date a date value
FMT is a date format specifier. The specified date will be processed into the form specified in this setup. This parameter is omitted, the specified date will be processed to the nearest day.
Below is the usage of this function:
Round (TO_DATE ('24-NOV-1999 08:00 PM ',' DD-MON-YYYY HH: MI AM '))
= '25-NOV-1999 12:00:00 AM '
Round (to_date)
24-NOV-1999
08:37 PM ',' DD-MON-YYYY HH: MI AM '),' HH '
= '24-Nov-1999 09:00:00 AM '
Note: This processing process may mean the change in date value. In the first example, since 8:00 Pm is a precise value next to the second day, the processing result is rotated to the next day (25th) instead of the day (24th).
SQL built-in function
• Round (for Number)
The Round function handles a numeric value (rounding) into the decimal number of the form specified by the second parameter.
The specific syntax format is as follows:
Round (Value, Places)
among them:
VALUE attempts to convert the value
The decimal position in the results of the item. Must be a positive integer, but it may be a negative number. If it is a negative number, the number is accurately processed in the number of decimers from the decimal point.
Below is the usage of this function:
Round (89, 985, 2) = 89.90 (Note That .005 is Rounded Up.)
Round (89, 985, -1) = 90
Round (89, 985, -2) = 100
SQL built-in function
• Rowidtochar
The RowidTochar function converts a row flag code (ROWID) into a string.
The specific syntax format is as follows:
Rowidtochar (RowID)
among them:
Rowid RowID Data
Below is the usage of this function:
SQL> SELECT ROWIDTOCHAR (ROWID) from Dual
Rowidtochar (RowID)
------------
Aaaadcaabaaaavuaaaaa
SQL built-in function
• RPAD
The RPAD function adds a number of characters behind the string.
The specific syntax format is as follows:
RPAD (String, Numchars [, Padding])
among them:
String any varchar2 or char type data
Numchars function result string length Padding append character, single. Is optional, default is space
Below is the usage of this function:
RPAD ('Jenny', 10) = 'JENNY' (5 trailing spaces)
RPAD ('Jenny', 10, '*') = 'jenny *****'
RPAD ('Jenny', 10, '*!') = 'Jenny *! *! *'
SQL built-in function
• RTRIM
The RTRIM function compresses the trailing character of the skewers, often compresses the space from the specified string (first parameter).
The specific syntax format is as follows:
Rtrim (String [, Trimchars])
among them:
String any varchar2 or char type data
TrimChars prepares the crushing character (string)
Below is the usage of this function:
RTRIM ('Jeff') = 'Jeff' (Trailing Spaces Removed)
RTRIM ('jenny *****') = 'jeff'
RTRIM ('jenny *! *! *') = 'Jeff'
SQL built-in function
• SIGN
The SIGN function returns the symbol of the argument. Returns 0 for negative returns -1,0, the positive number returns 1.
The specific syntax format is as follows:
Sign (Value)
among them:
Value Enter the argument, you try to return it symbol
Below is the usage of this function:
SIGN (-100) = - 1
SIGN (0) = 0
SIGN (100) = 1
This function and the DECode function can be used, which indicates that the expression is different from the positive, negative or 0 of a value.
SQL built-in function
• Sin
The SIN function returns a sinusoidal value of an angle.
The specific syntax format is as follows:
Sin (Angle)
among them:
Angle is to solve its sinusoidal angle. Is a angle expression. Reference function CoS understands from an arc to an angle conversion
Below is the usage of this function:
SIN (90 * 3.1415926 / 180) = 1
Sin (0) = 0
SQL built-in function
• Soundex
The SoundEx function returns a pronunciation description of a string according to the following rules.
1, retain the first letter of the string
2. Delete all the letters below: A, E, H, I, O, U, W and Y.
3, any of the letters behind the first letter matches a number in the following rules:
B, f, p, v = 1
C, G, J, K, Q, S, X, Z = 2
D, t = 3
I = 4
m, n = 5
R = 6
4, the result is abbreviated and converted into a 4-bit number
The specific syntax format is as follows:
Soundex (String)
among them:
String arbitrary string
Below is the usage of this function:
Soundex ('Gennick') = 520 Soundex ('GENICK') = 520 Soundex ('Genyk') = 520
SQL built-in function
• Sort
The sort function returns a number of arithmetic square roots.
The specific syntax format is as follows:
Sort (Number)
among them:
Number tries to solve the value of its square root, non-negative number
Below is the usage of this function:
Sort (100) = 10 (BECAUSE 10 * 10-100) sort (100) = 3.1622777
SQL built-in function
Substr
Substr returns a string specified string.
The specific syntax format is as follows:
Substr (String, Start, Length)
among them:
String arbitrary string
The start position of the Start string. The first position is always matched to 1. This parameter can be a negative number, at this time, from the right side of the given string
Length is the length of the string. Is an option, ignore it, match the entire string
Below is the usage of this function:
Substr ('Jennyjeffjonathan', 6, 4) = 'JEFF'
Substr ('Jennyjeffjonathan', - 12, 4) = 'Jeff'
Substr ('Jennyjeffjonathan, - 8) =' Jonathan '
SQL built-in function
Substrb
Substrb returns a specified section of a string. This function is very similar to the SubStr, just the start and length of the function, both bytes, not based on characters. This difference is only meaningful when using a variety of different character sets.
The specific syntax format is as follows:
Substrb (String, Start [, Length])
among them:
String arbitrary string
The start position of the Start string. The first position is always matched to 1. This parameter can be negative, then the number of right skewers from the given string
Length is the length of the string. Is an option, ignoring it, matching the whole string
Below is the usage of this function:
Substrb ('Jennyjeffjonathan', 6, 4) = 'Jeff'
Substrb ('Jennyjeffjonathan', - 12, 4) = 'JEFF'
Substrb ('Jennyjeffjonathan', - 8) = 'Jonathan'
SQL built-in function
• Sysdate
The SYSDATE function returns the current date time until second.
The specific syntax format is as follows:
Sysdate
Sysdate has no parameters.
Below is the usage of this function:
SQL> SELECT SYSDATE from DUAL;
Sysdate
-------
24-NOV-1999
06:45:00 PM
Of course, the result will vary depending on the timing of the user calls the function.
SQL built-in function
Tan
The TAN function returns an angle positive cut value.
The specific syntax format is as follows:
Tan (Angle)
among them:
Angle is represented by an arc. Reference function CoS understands from an arc to an angle conversion
Below is the usage of this function:
Tan (225 * 3.1415926535 / 180) = 1
Tan (0) = 0
SQL built-in function
• Tanh
The TANH function returns an angle of hexadecimal positive cut.
The specific syntax format is as follows:
Tanh (Angle)
among them:
Angle is represented by an arc. Reference function CoS understands from an arc to an angle conversion
Below is the usage of this function:
Tanh (225 * 3.1415926535 / 180) =. 99922389tanh (0) = 0
SQL built-in function
• To_char (for dates)
The To_Char function converts a date data into a characteristic data.
The specific syntax format is as follows:
TO_CHAR (DATE [, FMT [, 'NLS_DATE_LANGUAGE = Language']])
among them:
Date any Date type data
FMT a date format specifier, which controls the characteristic result of the representative date data
Language uses the language that has an impact on the date of spelling, such as using the month, day, week, etc.
Below is the usage of this function:
SQL> SELECT TO_CHAR (SYSDATE, 'DD-MON-YYYY')
From Dual;
TO_CHAR (SYS)
-----
24-NOV-1999
SQL> SELECT TO_CHAR (Sysdate, 'Month', 'NLS_DATE_LANGUAGE = Spanish')
From Dual;
TO_CHAR (SY
-----
November
SQL built-in function
• To_char (for number)
The To_Char function converts a numeric data into one characteristic data.
The specific syntax format is as follows:
TO_CHAR (Date [, FMT [, NLsparams]])
among them:
Date any value data
FMT a digital format specifier, which controls the character results of the representative date data
NLSPARAMS digital language characteristics. This parameter is a string consisting of several of the following contents: NLS_NUMERIC_CHARACTERS = "DG", NLS_Currency = "currchar", NLS_TERRITORY = Territory
D and g represent a decimal symbol and separator (in the US as a comma). The currency value (one of the US ($) symbols in the United States is often used to represent money values. These two values must be placed in quotation marks
Below is the usage of this function:
TO_CHAR (123.45) = '123.45'
To_char (123456.78, '$ 999, 999.99) =' $ 123,456.78 '
TO_CHAR (123456.78, 'L999G999D999 ",
'NLS_NUMERIC_CHARACTERS = ",." NLS_CURRENCY = "!")
= '! 123.456, 78'
Note: There is no effect in NLS parameters, you must use l, g and d pass symbols, separators, decimal points, and more.
SQL built-in function
• To_date
The to_date function converts a characteristic data into a date data.
The specific syntax format is as follows:
TO_DATE (String [, FMT [, 'NLS_DATE_LANGUAGUAGUAGUAGUAGE']])
among them:
String string to be converted
FMT a date format specifier, which controls the processing method when converting characters, is also an option, ignoring it, uses the default way of using the database.
The language used by Language is also optional, which has an impact on the date spelling.
Below is the usage of this function:
The first TO_DATE function explains the given string into 8-NOV-1915;
The following shows the usage of the to_date function:
TO_DATE ('11 / 08/1915 ',' mm / dd / yy ') Next time the next call is interpreted as 11-NOV-1915;
TO_DATE ('11 / 08/1915 ',' DD / MM / YY ')
The last example explains the given string into 11-NOV-1915;
TO_DATE ('11-NOV-1915', 'mm / dd / yyyy')
SQL built-in function
• To_lob
The To_lob function converts a long or long RAW data into the following types: Clob, BLOB or NCLOB. This function can be used when only the LONG type data is used in the INSERT statement instead of LOB type data.
The specific syntax format is as follows:
TO_LOB (long_value)
among them:
LONG_VALUE is to be converted to a long or long RAW data for type data such as Clob or NClob. Long Raw type data transplains with BLOB data
Below is the usage of this function:
INSERT INTO New_TABLE (Clob_Value)
SELECT TO_LOB (Long_Value)
From old_table
SQL built-in function
• TO_NUMBER
The To_Number function converts a string into a number.
The specific syntax format is as follows:
TO_NUMBER (String [, FMT [, 'NLSPARAMS']])
among them:
String string to be converted
FMT a digital format description symbol. It will control the processing method during the conversion process
NLsparams language feature value. The component of the string parameter has the following: NLS_NUMERIC_CHARACTERS = "DG", NLS_Currency = "currchar", and nls_territory = territory
D and g represent a decimal symbol and separator (in the US as a comma). The currency value (one of the US ($) symbols in the United States is often used to represent money values. These two values must be placed in quotes
Below is the usage of this function:
The first TO_NUMBER function explains the given string into 123.45;
TO_NUMBER ('123.45')
The next call will be interpreted as 123, 456.78
TO_NUMBER ('$ 123, 456.78', '$ 999, 999.99')
SQL built-in function
• TRIM
The TRIM function deletes the prefix (or missed) character of the string.
The specific syntax format is as follows:
Trim ([Leading | Trailing | Both] [Trimchar from] string)
among them:
Leading Indicates that only the prefix character of the string is deleted
Trailing Indicates that only the end of the string is deleted
Both indicates that both the prefix character and delete the tail. This is also the default method
String any of the string
TrimChar option. Indicates what characters attempt to delete, the default deleted character is space
Below is the usage of this function:
Trim ('Ashley') = 'Ashley'
Trim (Leading '*' from '*** Ashley ***') = 'Ashley ***'
SQL built-in function
• Trunc (for Dates)
The TRUNC function is a date value cut in the specified element.
The specific syntax format is as follows:
Trunc (Date [, FMT]):
Date a date value
FMT date format, this date will be cut by the specified element format. Ignore it, by the recent date
Below is the usage of this function:
Trunc (TO_DATE ('24-NOV-1999 08:00 PM ',' DD-MON-YYYY HH: MI AM '))
= '24-NOV-1999 12:00:00 AM '
Trunc (TO_DATE ('24-Nov-1999 08:37 PM ',' DD-MON-YYYY HH: MI AM ',' HH ')) = '24-NOV-1999 08:00:00 AM'
SQL built-in function
• Trunc (for Number)
The TRUNC function returns the value after processing, its working mechanism is very similar to the Round function, just that the function does not work corresponding to the corresponding part of the specified decimal or afterwards, and is intercepted.
The specific syntax format is as follows
Trunc (Number [, Decimals])
among them:
NUMBER Take the value of interception processing
Decimals indicates that the number of bits behind the decimal point must be retained. Optional option, ignore it, cut off all the fractional parts
Below is the usage of this function:
Trunc (89.985, 2) = 89.98
Trunc (89.985) = 89
Trunc (89.985, -1) = 80
Note: The second parameter can be a negative number, indicating that the portion of the number of decigitors indicating the number of positions in the left side of the position, namely 0 remembers.
SQL built-in function
• UID
The UID function returns an integer value. It is unique to the current database user.
The specific syntax format is as follows:
UID
This function has no parameters
Below is the usage of this function:
SQL> SELECT UID from Dual;
UID
--------
5
This value comes from the User # column in the V $ Session.
SQL built-in function
• Upper
Returns the uppercase of the string between the Upper function.
The specific syntax format is as follows:
Upper (String)
among them:
String Arbitrary VARCHAR2 or CHAR type string
Below is the usage of this function:
Upper ('this is a test') = 'this is a test'
SQL built-in function
User
User returns the current user name, no parameters.
The specific syntax format is as follows:
User
Below is the usage of this function:
SQL> SELECT User from Dual;
User
------------
SYSTEM
When e-embedded functions or programs are called, the function returns to the name of the caller.
