count occurence of character via sql

Have you ever encountered a requirement where you need to count the number of occurence of a particular character in a string? When I did, I initially thought that it is just straightforward and can be done in one command. So long as I know that command, it could be easy. Oh boy, was I so wrong. It's easy but still need some clever thinking. Here's the code.

SELECT raw_record, length(raw_record), length(raw_record) - length(replace(raw_record,'|','')) FROM MK_EAI_BATCH_TXN_DTL
WHERE txn_id = 'MK023120120509140106';

How does it work?

Here's what happens

  1. Count the number of characters of the original string

  2. Replace the character to count with ''

  3. Count the remaining characters

  4. Get the difference between the two counts, that's the number of occurence

Obviously, it only works for Latin character set. Special characters(chinese, arabic, etc) are unfortunately not supported by the code above. The above code also does not work for multi-characters. For that, you can use the code below.

SELECT raw_record,
(length(raw_record), length(raw_record) - length(replace(raw_record,'AA','')))/LENGTH('AA')
WHERE txn_id = 'MK023120120509140106';

Actually, this second script can be used for single and multiple characters. The only difference here is the addition of the division by the number of search characters.