SQL 如何计算 Oracle varchar 值中某个字符的出现次数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8169471/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to count the number of occurrences of a character in an Oracle varchar value?
提问by Ula Krukar
How can I count number of occurrences of the character -in a varchar2 string?
如何计算-varchar2 字符串中字符出现的次数？
Example:
例子：
select XXX('123-345-566', '-') from dual;
----------------------------------------
2
回答by Flukey
Here you go:
干得好：
select length('123-345-566') - length(replace('123-345-566','-',null))
from dual;
Technically, if the string you want to check contains only the character you want to count, the above query will return NULL; the following query will give the correct answer in all cases:
从技术上讲，如果您要检查的字符串仅包含您要计数的字符，则上述查询将返回 NULL；以下查询将在所有情况下给出正确答案：
select coalesce(length('123-345-566') - length(replace('123-345-566','-',null)), length('123-345-566'), 0)
from dual;
The final 0 in coalescecatches the case where you're counting in an empty string (i.e. NULL, because length(NULL) = NULL in ORACLE).
中的最后一个 0coalesce捕捉您在空字符串中计数的情况（即 NULL，因为在 ORACLE 中 length(NULL) = NULL）。
回答by Borodin
REGEXP_COUNTshould do the trick:
REGEXP_COUNT应该可以解决问题：
select REGEXP_COUNT('123-345-566', '-') from dual;
回答by bpgergo
Here's an idea: try replacing everything that is not a dash char with empty string. Then count how many dashes remained.
这是一个想法：尝试用空字符串替换不是破折号字符的所有内容。然后数一下还有多少破折号。
select length(regexp_replace('123-345-566', '[^-]', '')) from dual
回答by GrzegorzD
I justed faced very similar problem... BUT RegExp_Count couldn't resolved it. How many times string '16,124,3,3,1,0,' contains ',3,'? As we see 2 times, but RegExp_Count returns just 1. Same thing is with ''bbaaaacc' and when looking in it 'aa' - should be 3 times and RegExp_Count returns just 2.
我刚刚面临非常相似的问题......但 RegExp_Count 无法解决它。字符串 '16,124,3,3,1,0,' 包含 ',3,' 多少次？正如我们看到的 2 次，但 RegExp_Count 仅返回 1。与 ''bbaaaacc' 相同，当查看它时，'aa' - 应该是 3 次，而 RegExp_Count 仅返回 2。
select REGEXP_COUNT('336,14,3,3,11,0,' , ',3,') from dual;
select REGEXP_COUNT('bbaaaacc' , 'aa') from dual;
I lost some time to research solution on web. Couldn't' find... so i wrote my own function that returns TRUE number of occurance. Hope it will be usefull.
我失去了一些时间来研究网络上的解决方案。找不到……所以我写了自己的函数来返回真实的出现次数。希望它会很有用。
CREATE OR REPLACE FUNCTION EXPRESSION_COUNT( pEXPRESSION VARCHAR2, pPHRASE VARCHAR2 ) RETURN NUMBER AS
vRET NUMBER := 0;
vPHRASE_LENGTH NUMBER := 0;
vCOUNTER NUMBER := 0;
vEXPRESSION VARCHAR2(4000);
vTEMP VARCHAR2(4000);
BEGIN
vEXPRESSION := pEXPRESSION;
vPHRASE_LENGTH := LENGTH( pPHRASE );
LOOP
vCOUNTER := vCOUNTER + 1;
vTEMP := SUBSTR( vEXPRESSION, 1, vPHRASE_LENGTH);
IF (vTEMP = pPHRASE) THEN
vRET := vRET + 1;
END IF;
vEXPRESSION := SUBSTR( vEXPRESSION, 2, LENGTH( vEXPRESSION ) - 1);
EXIT WHEN ( LENGTH( vEXPRESSION ) = 0 ) OR (vEXPRESSION IS NULL);
END LOOP;
RETURN vRET;
END;
回答by Hugh Jones
I thought of
我想到了
SELECT LENGTH('123-345-566') - LENGTH(REPLACE('123-345-566', '-', '')) FROM DUAL;
回答by stefan
here is a solution that will function for both characters and substrings:
这是一个适用于字符和子字符串的解决方案：
select (length('a') - nvl(length(replace('a','b')),0)) / length('b')
from dual
where a is the string in which you search the occurrence of b
其中 a 是您在其中搜索 b 出现的字符串
have a nice day!
祝你今天过得愉快！
回答by Kunal
select count(*)
from (
select substr('K_u_n_a_l',level,1) str
from dual
connect by level <=length('K_u_n_a_l')
)
where str ='_';
回答by Abel Pinto
SELECT {FN LENGTH('123-345-566')} - {FN LENGTH({FN REPLACE('123-345-566', '#', '')})} FROM DUAL
回答by Riccardo D'Ippolito
You can try this
你可以试试这个
select count( distinct pos) from
(select instr('123-456-789', '-', level) as pos from dual
connect by level <=length('123-456-789'))
where nvl(pos, 0) !=0
it counts "properly" olso for how many 'aa' in 'bbaaaacc'
它“正确地”计算“bbaaaacc”中有多少“aa”
select count( distinct pos) from
(select instr('bbaaaacc', 'aa', level) as pos from dual
connect by level <=length('bbaaaacc'))
where nvl(pos, 0) !=0
