SQL to get total count for each colon delimited entry

SQL> desc datcon

Name                                        Null?    Type
------------------------------------------- --------
------------------------------------
ID                                                   NUMBER
CODE                                                 VARCHAR2(20)
CCODE                                                NUMBER

SQL> select * from datcon;

        ID CODE                      CCODE


---------- -------------------- ----------

         1 rc:jh:nb
         2 fg:aq:dx:xq
         3 jo

I would like to populate ccode with the total count of colon separated code for each ID. So based on the above example, I would have

        ID CODE                      CCODE


---------- -------------------- ----------

         1 rc:jh:nb			 3
         2 fg:aq:dx:xq			 4
         3 jo				 1

I'm not sure what would be the easiest way of writing an update statement to update the ccode. I am thinking about using plsql to count each colon delimited entry by looping through each Code record using the substr function but that seems very tedious. Can someone suggest a better method?

thanks. Received on Thu Jan 21 2010 - 13:38:22 CST