计算Oracle SQL中字符出现的次数 [英] Counting the number of occurrences of a character in Oracle SQL

问题描述

如何计算特定字符在Oracle列中出现的次数?例如，如果我有一个表FOO，该表具有类似a,ABC,def和2,3,4,5的数据，我想计算逗号在数据中出现的次数.

How can I count the number of times that a particular character occurs in a column in Oracle? For example, if I have a table FOO that has data like a,ABC,def and 2,3,4,5, I want to count the number of times that a comma appears in the data.

CREATE TABLE foo (
  str varchar2(30)
);

INSERT INTO foo VALUES( 'a,ABC,def' );
INSERT INTO foo VALUES( '2,3,4,5' );
commit;

我想要的输出是

str         cnt
a,ABC,def   2
2,3,4,5     3

推荐答案

通常的技巧之一是结合使用length和replace:

One of the usual tricks for this is to use a combination of length and replace:

select (length(your_col) - length(replace(your_col, ','))) from your_table;

replace 而没有第三个参数，只会删除该字符.

replace without a third argument will simply remove the character.

这篇关于计算Oracle SQL中字符出现的次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！