关于如何取一个字符串中某个字符出现的次数和最后出现的位置

#关于如何取一个字符串中某个字符出现的次数和最后出现的位置| 来源: 网络整理| 查看: 265

str为整个字符串

一个字符串中某个字符出现的次数：length(trim(translate(str, replace(str,','),' ')))

最后出现的位置：instr(str,'xx',-1)

instr使用说明：

The INSTR functions search string for substring. The search operation is defined as comparing the substring argument with substrings of string of the same length for equality until a match is found or there are no more substrings left. Each consecutive compared substring of string begins one character to the right (for forward searches) or one character to the left (for backward searches) from the first character of the previous compared substring. If a substring that is equal to substring is found, then the function returns an integer indicating the position of the first character of this substring. If no such substring is found, then the function returns zero.

position is an nonzero integer indicating the character of string where Oracle Database begins the search—that is, the position of the first character of the first substring to compare with substring. If position is negative, then Oracle counts backward from the end of string and then searches backward from the resulting position.

occurrence is an integer indicating which occurrence of substring in string Oracle should search for. The value of occurrence must be positive. If occurrence is greater than 1, then the database does not return on the first match but continues comparing consecutive substrings of string, as described above, until match number occurrence has been found.

INSTR accepts and returns positions in characters as defined by the input character set, with the first character of string having position 1. INSTRB uses bytes instead of characters. INSTRC uses Unicode complete characters. INSTR2 uses UCS2 code points. INSTR4 uses UCS4 code points.

Both string and substring can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The value returned is of NUMBER data type.

Both position and occurrence must be of data type NUMBER, or any data type that can be implicitly converted to NUMBER, and must resolve to an integer. The default values of both position and occurrence are 1, meaning Oracle begins searching at the first character of string for the first occurrence of substring. The return value is relative to the beginning of string, regardless of the value of position.

Examples

The following example searches the string CORPORATE FLOOR, beginning with the third character, for the string "OR". It returns the position in CORPORATE FLOOR at which the second occurrence of "OR" begins:

SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) "Instring" FROM DUAL; Instring ---------- 14

In the next example, Oracle counts backward from the last character to the third character from the end, which is the first O in FLOOR. Oracle then searches backward for the second occurrence of OR, and finds that this second occurrence begins with the second character in the search string :

SELECT INSTR('CORPORATE FLOOR','OR', -3, 2) "Reversed Instring" FROM DUAL; Reversed Instring ----------------- 2

The next example assumes a double-byte database character set.

SELECT INSTRB('CORPORATE FLOOR','OR',5,2) "Instring in bytes" FROM DUAL; Instring in bytes ----------------- 27 ====================================================================== Syntax

Description of the illustration translate.gif Purpose TRANSLATE returns expr with all occurrences of each character in from_string replaced by its corresponding character in to_string. Characters in expr that are not in from_string are not replaced. The argument from_string can contain more characters than to_string. In this case, the extra characters at the end of from_string have no corresponding characters in to_string. If these extra characters appear in expr, then they are removed from the return value. If a character appears multiple times in from_string, then the to_string mapping corresponding to the first occurrence is used. You cannot use an empty string for to_string to remove all characters in from_string from the return value. Oracle Database interprets the empty string as null, and if this function has a null argument, then it returns null. To remove all characters in from_string, concatenate another character to the beginning of from_string and specify this character as the to_string. For example, TRANSLATE(expr, 'x0123456789', 'x') removes all digits from expr. TRANSLATE provides functionality related to that provided by the REPLACE function. REPLACE lets you substitute a single string for another single string, as well as remove character strings. TRANSLATE lets you make several single-character, one-to-one substitutions in one operation. This function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion.