如何计算 Oracle varchar 值中某个字符出现的次数?

2021-12-06 00:00:00 sql count oracle oracle10g

如何计算 varchar2 字符串中字符 - 的出现次数?

How can I count number of occurrences of the character - in a varchar2 string?

示例:

select XXX('123-345-566', '-') from dual;
----------------------------------------
2

推荐答案

给你:

select length('123-345-566') - length(replace('123-345-566','-',null)) 
from dual;

从技术上讲,如果你要检查的字符串只包含你要计数的字符,上面的查询将返回NULL;以下查询将在所有情况下给出正确答案:

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:

select coalesce(length('123-345-566') - length(replace('123-345-566','-',null)), length('123-345-566'), 0) 
from dual;

coalesce 中的最后一个 0 捕捉您在空字符串中计数的情况(即 NULL,因为在 ORACLE 中 length(NULL) = NULL).

The final 0 in coalesce catches the case where you're counting in an empty string (i.e. NULL, because length(NULL) = NULL in ORACLE).

相关文章