Processing strings by stored procedures

Character functions used by stored procedures to process strings -- return character values all functions receive family-type parameters (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 value for balanced variable storage: The VARCHAR2 value is limited to 2000 characters (4000 characters in ORACLE 8). The CHAR value is limited to 255 characters (2000 in ORACLE8) long type is 2 GBClob type is 4GB1, CHR Syntax: chr (x) function: return the character set in the database and X has an equivalent value. CHR and ASCII are a pair of inverse functions. After CHR conversion, the character is converted to ASCII and the original character is obtained. Usage: Procedural and SQL statements. 2. CONCAT Syntax: CONCAT (string1, string2) function: returns string1 and connects string2 later. Usage: Procedural and SQL statements. 3. INITCAP Syntax: INITCAP (string) function: returns the string of the first letter of each word in the string and the string of other letters in the word in the lower case. Words are separated by. spaces or letters and numbers. Characters that are not letters remain unchanged. Usage: Procedural and SQL statements. 4. LTRIM Syntax: LTRIM (string1, string2) function: returns string1 that deletes the character 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. 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 a string with the first letter of each word in the string and lowercase letters of other letters in the word, nlsparams 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 language sorting sequence. Usage: Procedural and SQL statements. 6. NLS_LOWER Syntax: NLS_LOWER (string [, nlsparams]) function: returns a string in lowercase for all letters in the string. 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 of NLS_LOWER is the same as that of LOWER. Usage location; procedural statements and SQL statements. 7. NLS_UPPER Syntax: nls_upper (string [, nlsparams]) function: returns a string in uppercase format for all letters in the string. 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 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 sub-strings in string with the optional replace_str. If replace_str is not specified, search_str will be deleted from all strings. 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 the X character length. If string2 is less than X characters in length, copy the string as needed. If string2 is more than X characters, only the X characters before string1 are used. If string2 is not specified, use spaces to fill it. X indicates that the display length can be longer than the actual length of the string. 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, which appears in string1 from the right, is set to a single space by default. the database will scan string1, starting 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. this is helpful for comparing two words with different spelling but with 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. if a is 0, it is considered to start with the first character. if it is a positive number, the return characters are calculated from the left 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 defaults to the entire 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: returns the function of replacing each character in from_str with a string after the corresponding character in to_str. 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 replacement characters. to_str cannot be blank. oracle considers the NULL String as NULL, and if any parameter in the TRANSLATE Is NULL, the result is also NULL. usage: Procedural and SQL statements. 14. UPPER Syntax: UPPER (string) function: returns an UPPER-case string. the characters that are not letters remain unchanged. if string is of the CHAR data type, the result is of the CHAR type. if string is of the VARCHAR2 type, the result is of the VARCHAR2 type. usage: Procedural and SQL statements. Character functions -- Return numbers. These functions accept the return result of character parameters. the parameter can be of the CHAR or VARCHAR2 type. although many results are actually integer values, the returned results are of the simple NUMBER type, and 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 string. note that this function is still called ASCII. although many character sets are not 7-bit ASCII. CHR and ASCII are opposite functions. the CHR returns the response character of the given character encoding. ASCII to get 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 string2 is included in string1. string1 is checked from the left and starts at a. If a is a negative number, string1 is scanned from the right. 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 is not found in a and B, 0 is returned. location Calculation is relative to the start position of string1, regardless of the value of a and B. usage: Procedural and SQL statements. 18. Merge B Syntax: Merge B (string1, string2 [a, [B]) function: it is the same as INSTR, but the operation uses byte for parameter characters. usage: Procedural and SQL statements. 19. LENGTH Syntax: LENGTH (string) function: return the LENGTH of the byte unit of string. the CHAR value is of the space 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 single-byte character sets, LENGTHB and LENGTH are the same. usage: Procedural and SQL statements. 21. NLSSORT Syntax: NLSSORT (string [, nlsparams]) function: obtains string bytes used to sort strings. all values are converted to byte strings, which ensures consistency between different databases. the role of Nlsparams is the same as that in NLS_INITCAP. if the parameter is ignored, the session is sorted by default. usage: Procedural and SQL statements.