Re: SQL to get total count for each colon delimited entry

"cbarak" <charlinbarak_at_gmail.com> a écrit dans le message de news: 0fb8b0a6-3dad-41ec-abf8-d91f8020aa18_at_33g2000vbe.googlegroups.com...
| hi,
| 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.
|

SQL> select length('rc:jh:nb')-length(replace('rc:jh:nb',':',''))+1 nb from dual;

        NB

         3

1 row selected.

Regards
Michel Received on Thu Jan 21 2010 - 13:42:17 CST