SQL & PL/SQL

In SQL Server a method that I sometimes use to count a given character in a string is something like:

declare @test table(string varchar(20))
insert into @test
select null union all
select 'abcdcfc ' union all
select 'abcdcfc c '

select
'''' + string + '''' string,
len(string+'ÿ') - len(replace(string+'ÿ', 'c', '')) char_count
from @test

/* string char_count */
/* ---------------------- ----------- */
/* NULL NULL */
/* 'abcdcfc ' 3 */
/* 'abcdcfc c ' 4 */


I can still use the REPLACE function in Oracle to get the job done with something like:

select
'''' || string || '''' string,
length(string) - length(replace(string, 'c', '')) as char_count
from
( select null as string from dual union all
select 'abcdcfc ' from dual union all
select 'abcdcfc c ' from dual
)

/* string char_count */
/* ---------------------- ----------- */
/* NULL NULL */
/* 'abcdcfc ' 3 */
/* 'abcdcfc c ' 4 */

Is there a way to do this better?


Kent Waldrop