Oracle stored Procedure Summary Two, string processing correlation function _oracle

The following information is from the Internet, posted a save for later use, one for everyone to share.
Character function--returns the character value
These functions all receive the arguments of the character family type (except CHR) and return the character value.
In addition to the special instructions, most of these functions return numeric values of type VARCHAR2.
The limit of the return type of a character function is the same as that of the base database type.
Maximum value stored by character variable:
VARCHAR2 value is limited to 2000 characters (ORACLE 8 is 4000 characters)
Char value is limited to 255 characters (2000 in ORACLE8)
The long type is 2GB
CLOB type is 4GB
1, CHR
Syntax: Chr (x)
Function: Returns characters that have equivalent values in the database character set and X. CHR and ASCII are a pair of inverse functions. After the conversion of the CHR character and then through the ASCII conversion and get the original word
Character.
Use Location: Procedural statements and SQL statements.
2, CONCAT
Syntax: CONCAT (STRING1,STRING2)
Function: Returns string1 and connects string2 at the back.
Use Location: Procedural statements and SQL statements.
3, Initcap
Syntax: Initcap (String)
Function: Returns a string with the first letter of each word capitalized and the other letter in the word lowercase. Words are separated by. Spaces or alphanumeric characters. It's not a letter.
The character does not change.
Use Location: Procedural statements and SQL statements.
4, LTRIM
Syntax: LTRIM (STRING1,STRING2)
Function: Returns the string1 that deletes the characters appearing in string2 from the left. String2 is set to a single space by default. The database scans the string1, starting at the far left. When
The first character that is not in the string2 is encountered and the result is returned. LTrim's behavior is similar to RTrim's.
Use Location: Procedural statements and SQL statements.
5, Nls_initcap
Syntax: Nls_initcap (String[,nlsparams])
Function: Returns a string string,nlsparams the first letter of each word and the other letter in the word lowercase
A different sort sequence is specified that differs from the default value of the session. If you do not specify a parameter, the function is the same as Initcap. Nlsparams can be used in the form of:
' Nls_sort=sort '
Here sort has a language sort sequence.
Use Location: Procedural statements and SQL statements.
6, Nls_lower
Syntax: Nls_lower (String[,nlsparams])
Function: Returns a string in which all letters in strings are lowercase. Characters that are not letters do not change.
The form of the Nlsparams parameter is the same as the use and the Nlsparams parameter in the Nls_initcap. If Nlsparams is not included, then Nls_lower's processing and
Lower the same.
Use location, procedural statements, and SQL statements.
7, Nls_upper
Syntax: Nls_upper (String[,nlsparams])
Function: Returns a string in the form of uppercase letters in the strings. Characters that are not letters do not change. The Nlsparams parameter is in the same form as the use and the Nls_initcap. If
No parameters are set, the Nls_upper function is the same as upper.
Use Location: Procedural statements and SQL statements.
8, REPLACE
Syntax: REPLACE (STRING,SEARCH_STR[,REPLACE_STR])
Function: Replace all substrings in string search_str with optional replace_str, and if REPLACE_STR is not specified, the substring of all strings in string
Search_str will all be deleted. Replace is a subset of the functionality provided by translate.
Use Location: Procedural statements and SQL statements.
9, Rpad
Syntax: Rpad (String1,x[,string2])
Function: Returns the string1 of a character inserted in a string2 at the position of x character length. If the length of the string2 is less than the X character, copy as needed. If string2
More than x characters, only the string1 preceding x characters are used. If string2 is not specified, fill with a space. X is using the display length to be longer than the actual length of the string
Be long. Rpad behaves the same way as Lpad, except that it is filled on the right side rather than on the left.
Use Location: Procedural statements and SQL statements.
10, RTRIM
Syntax: RTRIM (String1,[,string2])
Function: Returns the character string2 that appears in string1 from the right. String2 is set by default to a single space. The database scans the string1, starting from the right. When you meet
To the first character that is not in the string2, the result is returned the RTrim behaves like LTrim.
Use Location: Procedural statements and SQL statements.
11, SOUNDEX
Syntax: SOUNDEX (String)
Function: Returns the sound representation of a string. This is helpful for comparing two different spelling but similar pronunciation words.
Use Location: Procedural statements and SQL statements.
12, SUBSTR
Syntax: SUBSTR (String,a[,b])
Function: Returns a substring of string that starts with a letter of value a of type B. 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 edge is computed on the right. If B is a negative number, the returned character is computed from the end of the string, from right to left. If B does not exist, it will default 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 will first be an integer before processing.
Use Location: Procedural statements and SQL statements.
13, TRANSLATE
Syntax: TRANSLATE (STRING,FROM_STR,TO_STR)
Function: Returns a string that replaces each character in the occurrence of the from_str with the corresponding character in To_str. Translate is a superset of the functionality provided by replace.
If the from_str is longer than the to_str, characters in from_str that are not in To_str are removed from string because they do not have corresponding substitution characters. To_str cannot be empty
. Oracle considers an empty string null, and if any argument in translate is null, the result is null.
Use Location: Procedural statements and SQL statements.
14, UPPER
Syntax: UPPER (String)
Function: Returns an uppercase string. The character is not the same as the letter. If string is a char data type, the result is also a char type. If the string is of type VARCHAR2, then
The result is also VARCHAR2 type.
Use Location: Procedural statements and SQL statements.
Character function--Returns a number
These functions accept character arguments back to numeric results. Parameters can be char or VARCHAR2 type. Although many of the actual results are integer values, the return result is a simple number
Type, without defining any precision or scale range.
16, ASCII
Syntax: ASCII (String)
Function: The database character set returns the decimal representation of the first byte of a string. Note that the 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 encoded by the given character. ASCII gets the character encoding for the given character.
Use Location: Procedural statements and SQL statements.
17, INSTR
Syntax: INSTR (string1, string2[a,b])
Function: To get the location that contains string2 in the string1. When string1 is checked from the left, the starting position is a, if a is a negative number, then the string1 is started from the right.
Scan. The position of the second occurrence will be returned. Both A and B are set to 1 by default, which returns the first occurrence of the string2 in string1. If string2 is not in the rules A and B
Find, then return 0. The calculation of the position is relative to the start position of the string1, regardless of the value of A and B.
Use Location: Procedural statements and SQL statements.
18, INSTRB
Syntax: INSTRB (string1, string2[a,[b])
Function: The same as InStr, except that the position used for the parameter character of the operation is byte.
Use Location: Procedural statements and SQL statements.
19, LENGTH
Syntax: LENGTH (String)
Function: Returns the length of the byte unit of a string. The char value is filled with a space type, and if string is a data type char, its trailing spaces are computed to the middle of the string length.
If string is null, the return result is null instead of 0.
Use Location: Procedural statements and SQL statements.
20, LENGTHB
Syntax: LENGTHB (String)
Function: Returns the length of a string in bytes. For single-byte Character set LENGTHB and length are the same.
Use Location: Procedural statements and SQL statements.
21, Nlssort
Syntax: Nlssort (String[,nlsparams])
Function: Gets the string byte used to sort the string. All values are converted to byte strings, thus maintaining consistency between the different databases. The role of Nlsparams and
The same in Nls_initcap. If you omit the argument, the session uses the default sort.
Use Location: Procedural statements and SQL statements.