To count the occurrences of the letter "b" in the target strings we can use a query like this (the column INTERMEDIATE_STRING is included to demonstrate the inner workings of each solution).Ĭolumn intermediate_string format a12 heading "INTERMEDIATE|STRING" select target, regexp_replace( target, '', null ) as intermediate_string, NVL( LENGTH( REGEXP_REPLACE( TARGET, '', NULL ) ), 0 ) as pattern_count from targets TARGET INTERMEDIATE PATTERN STRING STRING COUNT - aaa (null) 0 a b c b 1 b b b b bbbb 4 bbb xx bbb 3 wdef def w (null) 0 defxdefdef (null) 0 (null) (null) 0 This query finds the total occurrences of the letters "a", "c", or "f" in the target strings. However, we can use the LENGTH and REGEXP_REPLACE functions to produce similar results. Select target, REGEXP_COUNT( TARGET, 'def' ) as pattern_count from targets TARGET PATTERN STRING COUNT - aaa 0 a b c 0 b b b b 0 bbb xx 0 wdef def w 2 defxdefdef 3 (null) (null) Count String Pattern Occurrences (Oracle 10g) While Oracle 10g supports most of the regular expression features described in this section it unfortunately does not support REGEXP_COUNT. Select target, REGEXP_COUNT( TARGET, '' ) as pattern_count from targets TARGET PATTERN STRING COUNT - aaa 3 a b c 2 b b b b 0 bbb xx 0 wdef def w 2 defxdefdef 3 (null) (null) To calculate the number of times a string like "def" occurs we use a query like this.
Select target, REGEXP_COUNT( TARGET, 'b' ) as pattern_count from targets TARGET PATTERN STRING COUNT - aaa 0 a b c 1 b b b b 4 bbb xx 3 wdef def w 0 defxdefdef 0 (null) (null) This query finds the total occurrences of the letters "a", "c", or "f" in the target strings. Count String Pattern Occurrences (Oracle 11g) To count the occurrences of the letter "b" in the target strings we can use a query like this.