SQL to get total count for each colon delimited entry

From: cbarak <charlinbarak_at_gmail.com>
Date: Thu, 21 Jan 2010 11:38:22 -0800 (PST)
Message-ID: <0fb8b0a6-3dad-41ec-abf8-d91f8020aa18_at_33g2000vbe.googlegroups.com>

I've a table with the following structure and data,

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

Original text of this message