Oracle character FunctionsDescription: The input value of a character function is of the character type and the return value is of the character or number type. It can be used directly in an SQL statement or in a PL/SQL block.
1. ASCII (n) FunctionsDescription: return the ASCII code of the string (the ASCII code of the first character is returned when the input is a string) Examples: Select ASCII ('A') "test", ASCII ('ours ') "test1" from dual;
2. CHR (n) FunctionDescription: return the character of the corresponding ASCII code (N must be a number) Examples: Select ASCII (54992) "test" from dual;
3. Concat (n, m) FunctionsDescription: connects N and M. N and M can be characters or strings. The function is the same as "|. Examples: Select Concat ('China', 'people') "test" from dual;
4. initcap (n) FunctionDescription: the first letter of each word in string N is capitalized, And the other lowercase letters (the rule for distinguishing words is by space or non-letter characters; Chinese characters can be entered, but it does not work) Examples: select initcap ('Chinese nationals ') "test", initcap ('My word') "test1", initcap ('My Chinese word') "Test2" from dual;
5. instr (chr1, chr2, [N, [m]) FunctionDescription: obtains the position where the string chr2 appears in the string chr1. N and m are optional. If n is omitted, the default value is 1. N indicates the start position of the Start search. When n is negative, the search starts from the end. M indicates the number of times the string appears. Examples: Select instr ('pplkoopijk ', 'k',-) "test" from dual; Note: WHEN n is negative, search from the end, however, the return value is still the position obtained by forward arrangement.
6. Length (n) FunctionDescription: return characters or string lengths. (If n is null, nll is returned. The returned length includes spaces.) Examples: Select length ('ppl') "test", length (null) "test1" from dual;
7. Lower (n) FunctionDescription: converts n to lowercase. Examples: select lower ('kkd ') "test" from dual;
8. lpad (chr1, N, [chr2]) FunctionDescription: Fill in the character chr2 on the left of chr1 so that the total length of the character is N. Optional. The default value is space. If the chr1 string is longer than N, N characters of chr1 are truncated from the left. Examples: Select lpad ('kkk ', 5) "test", lpad ('kkkkk', 4) "test1", lpad ('kkk ', 6, 'lll ') "Test2" from dual;
9. ltrim (CHR, [N]) FunctionDescription: removes any character from the N string contained on the left of the string CHR until a character not included in N appears. Examples: Select ltrim ('abcde', 'A') "test", ltrim ('abcde', 'B') "test1", ltrim ('abcdefg ', 'ba') "Test2" from dual;
10. nls_initcap (CHR, ['nls _ param']) FunctionDescription: upper-case CHR letters. Nls_param (optional) specifies the sorting method. (Schinese_radical_m (radicals, strokes), schinese_stroke_m (strokes, radicals), schinese_pinyin_m (pinyin) Examples: Select nls_initcap ('AB cde') "test ", nls_initcap ('a B c d E', 'nls _ sort = schinese_pinyin_m ') "test1" from dual;
11. nls_lower (CHR, ['nls _ param']) FunctionDescription: converts a string to lowercase. Nls_param (optional) specifies the sorting method. (Schinese_radical_m (radicals, strokes), schinese_stroke_m (strokes, radicals), pinyin) Examples: Select nls_lower ('abc') "test", nls_lower ('abc ', 'nls _ sort = schinese_pinyin_m ') "test1" from dual;
12. nlssort (COL, ['nls _ param']) FunctionDescription: sort the col field according to the method specified by nls_param. Examples: Select part_number from cux_om_part_all order by nlssort (part_number, 'nls _ sort = schinese_radical_m ')
13. nls_upper (CHR, ['nls _ param']) FunctionDescription: converts CHR to uppercase. Nls_param (optional) is used to specify the sorting rule examples: Select nls_upper ('ddddd ', 'nls _ sort = xdanish') from dual
14. regexp_replace (source_string, pattern, replace_string, position, occurtence, match_parameter) function (new 10g function)Description: String replacement function. It is equivalent to an enhanced replace function. Source_string specifies the source character expression; pattern specifies the rule expression; replace_string specifies the string to be replaced; position specifies the start search position; occurtence specifies the nth string to be replaced; match_parameter specifies the text string for the default matching operation. The replace_string, position, occurtence, and match_parameter parameters are optional.
15,Regexp_substr (source_string, pattern [, position [, occurrence [, match_parameter])Function (10 Gb new function)Description: return the substring of the matching mode. It is equivalent to the enhanced substr function. Source_string specifies the source character expression, pattern specifies the rule expression, position specifies the start search position, occurtence specifies the nth string to replace, and match_parameter specifies the text string of the default matching operation. The position, occurtence, and match_parameter parameters are optional examples: Select regexp_substr ('HTTP: // www.oracle.com/products', 'http:// ([[: alnum:] + /.?) {3, 4 }/? ') "Regexp_substr" from dual
16,Regexp_like (source_string, Pattern[, Match_parameter])Function (10 Gb new function)
Description: return a string that meets the matching mode. It is equivalent to an enhanced like function. Source_string specifies the source character expression, pattern specifies the rule expression, and match_parameter specifies the text string of the default matching operation. The position, occurtence, and match_parameter parameters are optional examples:
17,Regexp_instr (source_string, Pattern[, Start_position[, Occurrence[, Return_option[, Match_parameter])Function (10 Gb new function)
Description: This function searches for Pattern and returns the first position of the pattern. You can specify the start_position you want to start searching. The default occurrence parameter is 1, unless you specify the mode you want to find. The default value of return_option is 0, which returns the starting position of the mode. If the value is 1, the start position examples of the next character that meets the matching conditions is returned:
18. Replace (CHR, search_string, [, replacement_string]) FunctionDescription: Replace the CHR that meets the search_string condition with the string specified by replacement_string. If search_string is null, CHR is returned. If replacement_string is null, returns the string truncated from search_string in Chr. Examples: Select Replace ('abcdeef ', 'E', 'oo') "test", replace ('abcdeef ', 'ee', 'oo ') "test1 ", replace ('abcdeef ', null, 'oo') "Test2", replace ('abcdeef ', 'ee', null) "test3" from dual
19. rpad (chr1, N, chr2) FunctionsDescription: Fill in chr2 on the Right of chr1 to make the returned string length n .. When chr1 is longer than N, return n characters on the left. Refer to the lpad () function.
20. rtrim (CHR, [set]) FunctionDescription: removes any character from the set contained on the right side of the CHR until the occurrence of a character not in the set ends. Refer to the ltrim () function.
21. soundex (CHR) FunctionsDescription: returns the voice representation of a string. It can be used to compare whether the pronunciation of a string is the same. Examples: Select soundex ('ship ') "test", soundex ('sleep') "test1" from dual;
22. substr (CHR, M [, N]) FunctionDescription: The child string of the CHR. M indicates the start position, and N indicates the length to be taken. When M is 0, it starts from the first character and when m is negative, it is truncated from the end of the string. Examples: Select substr ('abcdef',) "test", substr ('abcdef',) "test1", substr ('abcdef',-) "Test2 ", substr ('abcdef ',-1, 3) "test3" from dual Note: When M is 0 or 1, the start position is the same, starting from the first, when m is negative, it is still in the order from left to right, so if M is-1 and N is longer, it can only be the last character, because there are no characters on the Right of Chr.
23. Translate (CHR, from_str, to_str) FunctionDescription: The usage of another replacement function. Examples: Select translate ('abcdeabc', 'abc', 'fgh') "test", translate ('abcdeabc', 'abc', 'hf ') "test1 ", translate ('abcdeabc', 'AB', 'hfgh') "Test2", translate ('abcdeabc', 'abc', '')" test3 "from dual note: the matching rules are from_str and to_str, which correspond to each character in sequence. If from_str is less than the character in to_str, only the corresponding character is replaced, if the character after to_str cannot match the character corresponding to from_str, if the character from_str is more than the character to_str, the corresponding character cannot be found in from_str and is processed as null.
24. Trim (CHR) FunctionsThe TRIM function deletes the string prefix (or trailing. The syntax format is as follows: trim ([leading | trailing | both] [trimchar from] string) where: leading indicates that only the character prefix of the string is deleted. Trailing indicates that only the trailing characters of the string are deleted. Both indicates that both the prefix and trailing characters are deleted. This is also the default method of string any string to be processed trimchar option. Specifies the characters to be deleted. By default, the characters to be deleted are spaces. The usage of this function is as follows: trim ('ashley ') = 'ashley 'trim (leading' * 'from' *** Ashley *** ') = 'ashley ***'
25. Upper (CHR) FunctionThe upper-case string format is returned between the upper functions. The syntax format is as follows: Upper (string) Where: String any varchar2 or char string is used in this function: Upper ('this is a test ') = 'this is a Test'
