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

From: cbarak <charlinbarak_at_gmail.com>
Date: Thu, 21 Jan 2010 12:08:44 -0800 (PST)
Message-ID: <9e874e9b-e4f5-4a2a-aef4-19d4555ca969_at_x9g2000vbo.googlegroups.com>

On Jan 21, 2:42 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "cbarak" <charlinba..._at_gmail.com> a écrit dans le message de news:
> 0fb8b0a6-3dad-41ec-abf8-d91f8020a..._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

Thanks Michel! Received on Thu Jan 21 2010 - 14:08:44 CST