Oracle stored procedures summary ii string processing related functions
- 2020-06-19 11:55:28
- OfStack
The following information is from the Internet. Post it, save it for future use, and share it.
Character function - Returns the character value
These functions all take arguments of character family type (except CHR) and return character values.
Except as otherwise specified, most of these functions return values of type VARCHAR2.
The return type of a character function is subject to the same restrictions as the base database type.
Maximum value of character variable storage:
VARCHAR2 is limited to 2000 characters (4000 characters in ORACLE 8)
The CHAR value is limited to 255 characters (2000 in ORACLE8)
The long type is 2GB
The Clob type is 4GB
1, CHR
Grammar: chr (x)
Function: Returns characters that have equivalent values to X in the database character set. CHR and ASCII are inverse functions of 1. Characters converted by CHR are converted by ASCII to return the original characters
Operator.
Use location: procedural statements and SQL statements.
2, CONCAT
Grammar: CONCAT (string1,string2)
Function: Return string1 and connect string2 later.
Use location: procedural statements and SQL statements.
3, INITCAP
Grammar: INITCAP (string)
Function: Returns string in uppercase for the first letter of each word in the string and lowercase for the rest of the words. Words are separated by. Spaces or alphanumeric characters. Non-alphabetic
The character does not change.
Use location: procedural statements and SQL statements.
4, LTRIM
Grammar: LTRIM (string1,string2)
Function: Returns string1 that deletes characters that appear in string2 from the left. String2 is set by default to a single space. The database will scan string1, starting at the far left. when
The result is returned when the first character not in string2 is encountered. LTRIM behaves in a similar way to RTRIM.
Use location: procedural statements and SQL statements.
5, NLS_INITCAP
Grammar: NLS_INITCAP (string[,nlsparams])
Function: Returns string, nlsparams, in uppercase for the first letter of each word in the string and lowercase for the other letters in the word
Specifies a different collation sequence than the default for the session. If no parameters are specified, the function is the same as INITCAP. Nlsparams can be used in the following forms:
'NLS_SORT = sort'
Here sort makes a language collation sequence.
Use location: procedural statements and SQL statements.
6, NLS_LOWER
Grammar: NLS_LOWER (string[,nlsparams])
Function: Returns string in lowercase for all letters in the string. Not alphabetic characters that don't change.
The FORM of the Nlsparams parameter is the same as the use and the nlsparams parameter in NLS_INITCAP. If nlsparams is not included, then the processing and by NLS_LOWER
The same LOWER.
Use position; Procedural statements and SQL statements.
7, NLS_UPPER
Syntax: nls_upper (string[,nlsparams])
Function: Returns string in which all letters in the string are uppercase. Not alphabetic characters that don't change. The nlsparams parameter takes the same form as the use and NLS_INITCAP. if
With no parameters set, NLS_UPPER has the same function as UPPER.
Use location: procedural statements and SQL statements.
8 REPLACE.
Grammar: REPLACE (string, search_str[,replace_str])
Function: Replaces all substrings in string search_str with the optional replace_str, if replace_str is not specified, all substrings in string
search_str will be deleted. REPLACE is a subset of the functionality provided by TRANSLATE.
Use location: procedural statements and SQL statements.
9 RPAD.
Grammar: RPAD (string1,x[,string2])
Function: Returns string1 for inserting 1 character in string2 at the position of X character length. If string2 has fewer characters than X, copy as needed. If string2
When more than X characters are used, only the X characters preceding string1 are used. If string2 is not specified, fill in with Spaces. X is used to show that the length can be greater than the actual length of the string
To long. RPAD behaves much like LPAD except that it fills on the right instead of the left.
Use location: procedural statements and SQL statements.
10, RTRIM
Grammar: RTRIM (string1,[,string2])
string2.string2 is set by default to a single space. The database will scan string1, starting from the right
By the first character not in string2, the result is returned. RTRIM behaves in a manner similar to LTRIM.
Use location: procedural and SQL statements.
11, SOUNDEX
Grammar: SOUNDEX (string)
Function: Returns the sound representation of string. This is useful for comparing two words with different spellings but similar sounds.
Use location: procedural and SQL statements.
12, SUBSTR
Grammar: SUBSTR (string,a[,b])
Function: Returns a substring of b from a. If a is 0, it is considered to start at the first character. If positive, the return character is from the left
If b is negative, the returned character is evaluated from right to left from the end of string. If b does not exist, it defaults to the entire character
String. If b is less than 1, then NULL is returned. If a or b USES a floating-point number, the value is first converted to an integer before processing.
Use location: procedural and SQL statements.
13, TRANSLATE
Grammar: TRANSLATE (string from_str, to_str)
Function: Returns a superset of string.TRANSLATE provided by REPLACE after replacing each character in from_str that appears with the corresponding character in to_str.
If from_str is longer than to_str, characters that are in from_str but not in to_str will be deleted from string because they have no corresponding substitution characters. to_str cannot be empty
.Oracle treats the empty string as NULL, and if any of the arguments in TRANSLATE are NULL, the result is also NULL.
Use location: procedural and SQL statements.
14, UPPER
Grammar: UPPER (string)
Function: Returns uppercase string. The character is not fixed. If string is of CHAR, the result is of CHAR. If string is of VARCHAR2, then
The result is also of type VARCHAR2.
Use location: procedural and SQL statements.
Character function - returns a number
These functions take character arguments and return numeric results. Arguments can be of type CHAR or VARCHAR2. Although in practice many results are integer values, the return results are simple NUMBER
Type, without defining any precision or scale range.
16, ASCII
Grammar: ASCII (string)
Feature: The database character set returns a decimal representation of the first byte of string. Note that this function is still referred to as ASCII. Although many character sets are not 7-bit ES300en. CHR and ASCII are each other
CHR gets the response character for the given character encoding. ASCII gets the character encoding for the given character.
Use location: procedural statements and SQL statements.
17, INSTR
Grammar: INSTR (string1, string2[a,b])
Function: Get the position containing string2 in string1. Check from the left when string1 starts with a. If a is a negative number, then string1 starts from the right
a and b are both set to 1 by default, which will return the location of string2 for the first occurrence of string1 if string2 is not available under a and b
Find, then the calculation that returns the 0. Position is relative to the starting position of string1, regardless of the values of a and b.
Use location: procedural statements and SQL statements.
In the 18th and INSTRB
Grammar: INSTRB (string1, string2[a,[b]])
Functionality: Same as INSTR, except that the position used for parameter characters is byte.
Use location: procedural statements and SQL statements.
19, LENGTH
Grammar: LENGTH (string)
Function: Returns the length of the byte unit of string. The CHAR value is filled with a space type. If string is a data type CHAR, the Spaces at the end of the string are computed in the middle of the string length.
If string is NULL, the result is NULL, not 0.
Use location: procedural statements and SQL statements.
20, LENGTHB
Grammar: LENGTHB (string)
Function: Returns the length of string in bytes. This is the same for single-byte character sets LENGTHB and LENGTH.
Use location: procedural statements and SQL statements.
21, NLSSORT
Grammar: NLSSORT (string[,nlsparams])
Function: Gets the string bytes used to sort string. All values are converted to byte strings so that 1 alignment is maintained between different databases. Nlsparams functions and
Same as in NLS_INITCAP. If parameters are ignored, the session USES the default sort.
Use location: procedural statements and SQL statements.
Character function - Returns the character value
These functions all take arguments of character family type (except CHR) and return character values.
Except as otherwise specified, most of these functions return values of type VARCHAR2.
The return type of a character function is subject to the same restrictions as the base database type.
Maximum value of character variable storage:
VARCHAR2 is limited to 2000 characters (4000 characters in ORACLE 8)
The CHAR value is limited to 255 characters (2000 in ORACLE8)
The long type is 2GB
The Clob type is 4GB
1, CHR
Grammar: chr (x)
Function: Returns characters that have equivalent values to X in the database character set. CHR and ASCII are inverse functions of 1. Characters converted by CHR are converted by ASCII to return the original characters
Operator.
Use location: procedural statements and SQL statements.
2, CONCAT
Grammar: CONCAT (string1,string2)
Function: Return string1 and connect string2 later.
Use location: procedural statements and SQL statements.
3, INITCAP
Grammar: INITCAP (string)
Function: Returns string in uppercase for the first letter of each word in the string and lowercase for the rest of the words. Words are separated by. Spaces or alphanumeric characters. Non-alphabetic
The character does not change.
Use location: procedural statements and SQL statements.
4, LTRIM
Grammar: LTRIM (string1,string2)
Function: Returns string1 that deletes characters that appear in string2 from the left. String2 is set by default to a single space. The database will scan string1, starting at the far left. when
The result is returned when the first character not in string2 is encountered. LTRIM behaves in a similar way to RTRIM.
Use location: procedural statements and SQL statements.
5, NLS_INITCAP
Grammar: NLS_INITCAP (string[,nlsparams])
Function: Returns string, nlsparams, in uppercase for the first letter of each word in the string and lowercase for the other letters in the word
Specifies a different collation sequence than the default for the session. If no parameters are specified, the function is the same as INITCAP. Nlsparams can be used in the following forms:
'NLS_SORT = sort'
Here sort makes a language collation sequence.
Use location: procedural statements and SQL statements.
6, NLS_LOWER
Grammar: NLS_LOWER (string[,nlsparams])
Function: Returns string in lowercase for all letters in the string. Not alphabetic characters that don't change.
The FORM of the Nlsparams parameter is the same as the use and the nlsparams parameter in NLS_INITCAP. If nlsparams is not included, then the processing and by NLS_LOWER
The same LOWER.
Use position; Procedural statements and SQL statements.
7, NLS_UPPER
Syntax: nls_upper (string[,nlsparams])
Function: Returns string in which all letters in the string are uppercase. Not alphabetic characters that don't change. The nlsparams parameter takes the same form as the use and NLS_INITCAP. if
With no parameters set, NLS_UPPER has the same function as UPPER.
Use location: procedural statements and SQL statements.
8 REPLACE.
Grammar: REPLACE (string, search_str[,replace_str])
Function: Replaces all substrings in string search_str with the optional replace_str, if replace_str is not specified, all substrings in string
search_str will be deleted. REPLACE is a subset of the functionality provided by TRANSLATE.
Use location: procedural statements and SQL statements.
9 RPAD.
Grammar: RPAD (string1,x[,string2])
Function: Returns string1 for inserting 1 character in string2 at the position of X character length. If string2 has fewer characters than X, copy as needed. If string2
When more than X characters are used, only the X characters preceding string1 are used. If string2 is not specified, fill in with Spaces. X is used to show that the length can be greater than the actual length of the string
To long. RPAD behaves much like LPAD except that it fills on the right instead of the left.
Use location: procedural statements and SQL statements.
10, RTRIM
Grammar: RTRIM (string1,[,string2])
string2.string2 is set by default to a single space. The database will scan string1, starting from the right
By the first character not in string2, the result is returned. RTRIM behaves in a manner similar to LTRIM.
Use location: procedural and SQL statements.
11, SOUNDEX
Grammar: SOUNDEX (string)
Function: Returns the sound representation of string. This is useful for comparing two words with different spellings but similar sounds.
Use location: procedural and SQL statements.
12, SUBSTR
Grammar: SUBSTR (string,a[,b])
Function: Returns a substring of b from a. If a is 0, it is considered to start at the first character. If positive, the return character is from the left
If b is negative, the returned character is evaluated from right to left from the end of string. If b does not exist, it defaults to the entire character
String. If b is less than 1, then NULL is returned. If a or b USES a floating-point number, the value is first converted to an integer before processing.
Use location: procedural and SQL statements.
13, TRANSLATE
Grammar: TRANSLATE (string from_str, to_str)
Function: Returns a superset of string.TRANSLATE provided by REPLACE after replacing each character in from_str that appears with the corresponding character in to_str.
If from_str is longer than to_str, characters that are in from_str but not in to_str will be deleted from string because they have no corresponding substitution characters. to_str cannot be empty
.Oracle treats the empty string as NULL, and if any of the arguments in TRANSLATE are NULL, the result is also NULL.
Use location: procedural and SQL statements.
14, UPPER
Grammar: UPPER (string)
Function: Returns uppercase string. The character is not fixed. If string is of CHAR, the result is of CHAR. If string is of VARCHAR2, then
The result is also of type VARCHAR2.
Use location: procedural and SQL statements.
Character function - returns a number
These functions take character arguments and return numeric results. Arguments can be of type CHAR or VARCHAR2. Although in practice many results are integer values, the return results are simple NUMBER
Type, without defining any precision or scale range.
16, ASCII
Grammar: ASCII (string)
Feature: The database character set returns a decimal representation of the first byte of string. Note that this function is still referred to as ASCII. Although many character sets are not 7-bit ES300en. CHR and ASCII are each other
CHR gets the response character for the given character encoding. ASCII gets the character encoding for the given character.
Use location: procedural statements and SQL statements.
17, INSTR
Grammar: INSTR (string1, string2[a,b])
Function: Get the position containing string2 in string1. Check from the left when string1 starts with a. If a is a negative number, then string1 starts from the right
a and b are both set to 1 by default, which will return the location of string2 for the first occurrence of string1 if string2 is not available under a and b
Find, then the calculation that returns the 0. Position is relative to the starting position of string1, regardless of the values of a and b.
Use location: procedural statements and SQL statements.
In the 18th and INSTRB
Grammar: INSTRB (string1, string2[a,[b]])
Functionality: Same as INSTR, except that the position used for parameter characters is byte.
Use location: procedural statements and SQL statements.
19, LENGTH
Grammar: LENGTH (string)
Function: Returns the length of the byte unit of string. The CHAR value is filled with a space type. If string is a data type CHAR, the Spaces at the end of the string are computed in the middle of the string length.
If string is NULL, the result is NULL, not 0.
Use location: procedural statements and SQL statements.
20, LENGTHB
Grammar: LENGTHB (string)
Function: Returns the length of string in bytes. This is the same for single-byte character sets LENGTHB and LENGTH.
Use location: procedural statements and SQL statements.
21, NLSSORT
Grammar: NLSSORT (string[,nlsparams])
Function: Gets the string bytes used to sort string. All values are converted to byte strings so that 1 alignment is maintained between different databases. Nlsparams functions and
Same as in NLS_INITCAP. If parameters are ignored, the session USES the default sort.
Use location: procedural statements and SQL statements.