APEX-AT-WORK no image

SQL: Count character in column or string

Von Tobias Arnhold 3.21.2011
Use this sql select to count a special character inside a column or string:

Column selection:

select col, length(col)-length(replace(col,',',''))
from
(select 'col1,col2,col3,col4' as col from dual);

COL LENGTH(COL)-LENGTH(REPLACE(COL,',',''))
------------------- ---------------------------------------
col1,col2,col3,col4 3

String selection

BEGIN
:P1_STRING := 'col1,col2,col3,col4';

select length(:P1_STRING)-length(replace(:P1_STRING,',','')) into :P1_CHAR_CNT from dual;
END;


Watch this thread on Ask Tom: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:55423584511523

Post Tags:

1 One Comment " SQL: Count character in column or string "

Tim... 21 March, 2011 19:06

If you are using 11g, you can just use the REGEXP_COUNT function. :)

http://www.oracle-base.com/articles/11g/PlsqlNewFeaturesAndEnhancements_11gR1.php#ehancements_to_regular_expressions

Cheers

Tim...