Oracle Stored Procedure Summary ii. string processing functions

The following information is from the Internet. You can paste it and save it for future use.
Character function -- returns the character value
All these functions receive parameters of the character family type (except for CHR) and return character values.
Except for some special descriptions, most of these functions return VARCHAR2 values.
The restrictions on the return type of the character function are the same as those on the basic database type.
Maximum variable storage capacity:
The VARCHAR2 value 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 2 GB.
Clob type is 4 GB
1. CHR
Syntax: chr (x)
Function: returns the characters in the database character set that are equivalent to X. CHR and ASCII are a pair of inverse functions. After CHR conversion, the character is converted to ASCII and the original word is obtained.
.
Usage: Procedural and SQL statements.
2. CONCAT
Syntax: CONCAT (string1, string2)
Function: returns string1 and connects to string2.
Usage: Procedural and SQL statements.
3. INITCAP
Syntax: INITCAP (string)
Function: returns the first letter of each word of a string in upper case, and the other letters in the word in lower case. Words are separated by. spaces or letters and numbers. Not a letter
The characters remain unchanged.
Usage: Procedural and SQL statements.
4. LTRIM
Syntax: LTRIM (string1, string2)
Function: returns the string1 string that appears in string2 from the left. String2 is set to a single space by default. The database will scan string1, starting from the leftmost. When
If the first character is not in string2, the result is returned. The behavior of LTRIM is similar to that of RTRIM.
Usage: Procedural and SQL statements.
5. NLS_INITCAP
Syntax: NLS_INITCAP (string [, nlsparams])
Function: returns the string, nlsparams, in lowercase of the first letter of each word and the other letters in the word.
Specifies different sorting sequence different from the default value of the session. If no parameter is specified, the function is the same as that of INITCAP. The form that Nlsparams can use is:
'Nls _ SORT = sort'
Here, sort develops a sequence of language sorting.
Usage: Procedural and SQL statements.
6. NLS_LOWER
Syntax: NLS_LOWER (string [, nlsparams])
Function: returns the string in lowercase. The characters that are not letters remain unchanged.
The form and purpose of the Nlsparams parameter are the same as that of the nlsparams parameter in NLS_INITCAP. If nlsparams is not included, the processing and
The LOWER is the same.
Usage location; procedural statements and SQL statements.
7. NLS_UPPER
Syntax: nls_upper (string [, nlsparams])
Function: returns a string of uppercase letters. The characters that are not letters remain unchanged. The form and purpose of the nlsparams parameter are the same as those in NLS_INITCAP. If
If no parameter is set, the NLS_UPPER function is the same as the UPPER function.
Usage: Procedural and SQL statements.
8. REPLACE
Syntax: REPLACE (string, search_str [, replace_str])
Function: replace all the substrings in string search_str with the optional replace_str. If replace_str is not specified
Search_str will be deleted. REPLACE is a subset of the functions provided by TRANSLATE.
Usage: Procedural and SQL statements.
9. RPAD
Syntax: RPAD (string1, x [, string2])
Function: returns the string1 of the characters inserted in string2 at the position of X characters. If string2 is less than X characters in length, copy the string as needed. If string2
If the number is greater than X, only the characters before string1 are used. If string2 is not specified, use spaces to fill it. X indicates the actual length of a string.
Long. The behavior of RPAD is similar to that of LPAD, except that it is filled on the right rather than on the left.
Usage: Procedural and SQL statements.
10. RTRIM
Syntax: RTRIM (string1, [, string2])
Function: returns the string string2. string2 that appears in string1 from the right. string2 is set to a single space by default. The database will scan string1 and start from the right.
When the first character is not in string2, the result is returned. The RTRIM behavior is similar to LTRIM.
Usage: Procedural and SQL statements.
11. SOUNDEX
Syntax: SOUNDEX (string)
Function: returns the string sound representation, which is helpful for comparing two words with different spelling but similar pronunciation.
Usage: Procedural and SQL statements.
12. SUBSTR
Syntax: SUBSTR (string, a [, B])
Function: returns a substring of a string of B characters starting from letter. if a is 0, it is considered to start with the first character. if it is a positive number, the return character is from the left
The side is calculated to the right. if B is a negative number, the returned characters are calculated from the right to the left starting from the end of the string. if B does not exist, it sets the default value to the entire character.
String. If B is less than 1, NULL is returned. If a or B uses a floating point number, the value is regarded as an integer before processing.
Usage: Procedural and SQL statements.
13. TRANSLATE
Syntax: TRANSLATE (string, from_str, to_str)
Function: REPLACE each character in from_str with the corresponding character in to_str and then string. TRANSLATE Is a superset of the functions provided by REPLACE.
If from_str is longer than to_str, characters not included in to_str in from_str will be deleted from the string because they do not have a replacement character. to_str cannot be blank.
Oracle considers the NULL String as NULL, and if any parameter in TRANSLATE is NULL, the result is also NULL.
Usage: Procedural and SQL statements.
14. UPPER
Syntax: UPPER (string)
Function: returns an uppercase string. If the string is of the CHAR data type, the result is of the CHAR type. If the string is of the VARCHAR2 type
The result is of the VARCHAR2 type.
Usage: Procedural and SQL statements.
Character function -- returns a number
These functions receive numeric results from character parameters. The parameters can be of the CHAR or VARCHAR2 type. Although many results are integers, the returned results are simple numbers.
Type. No precision or scale range is defined.
16. ASCII
Syntax: ASCII (string)
Function: the database character set returns the decimal representation of the first byte of the string. Please note that this function is still called ASCII. Although many character sets are not 7-bit ASCII. CHR and ASCII are mutually
The opposite function. CHR gets the response character of the given character encoding. ASCII gets the character encoding of the given character.
Usage: Procedural and SQL statements.
17. INSTR
Syntax: INSTR (string1, string2 [a, B])
Function: Obtain the position where string1 contains string2. when string1 is checked from the left, the start position is a. If a is a negative number, string1 starts from the right.
Scanned. the location where B appears will be returned. both a and B are set to 1 by default, which will return the position where string2 appears for the first time in string1. if string2 does not have
If it is found, the return value is 0. The calculation of the position is relative to the start position of string1, regardless of the value of a and B.
Usage: Procedural and SQL statements.
18. region B
Syntax: Limit B (string1, string2 [a, [B])
Function: it is the same as INSTR, but the parameter character is operated in bytes.
Usage: Procedural and SQL statements.
19. LENGTH
Syntax: LENGTH (string)
Function: return the length of the string byte unit. The CHAR value is of the space filling type. If the string is of the data type CHAR, the spaces at the end of the string are calculated to the middle of the string length.
If string is NULL, the returned result is NULL instead of 0.
Usage: Procedural and SQL statements.
20. LENGTHB
Syntax: LENGTHB (string)
Function: returns the LENGTH of a string in bytes. For a single-byte character set, LENGTHB and LENGTH are the same.
Usage: Procedural and SQL statements.
21. NLSSORT
Syntax: NLSSORT (string [, nlsparams])
Function: Obtain the string byte used to sort strings. All values are converted to byte strings, so consistency is maintained between different databases. The functions and
The same in NLS_INITCAP. If the parameter is ignored, the session uses the default sorting.
Usage: Procedural and SQL statements.