count number of character occurrrnce i a string

bono56 · #1 (**permalink**) 10-20-07, 20:06

hi
how can i select number of a character occurrrnce in a string?
for example in "bono", number of "o" is 2.

r937 · #2 (**permalink**) 10-20-07, 22:39

use the REPLACE function to remove the character from the column value (by replacing it with an empty string)

compare the length of the replaced string with the length of the original column value

the difference is the number of characters that were removed, i.e. the number of characters in the original

Code:

select length('bono') - length(replace('bono','o','')) as number_of_characters

aschk · #3 (**permalink**) 10-22-07, 11:40

That's nice rudy!