SQL to get total count for each colon delimited entry
in [Oracle]
|
Prev: Run 32-bit and 64-bit listener at the same time on W3K 64-bit
Next: 10rR2 automatically re-creates missing TEMP files
From: cbarak on 21 Jan 2010 14:38 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.
From: Michel Cadot on 21 Jan 2010 14:42 "cbarak" <charlinbarak(a)gmail.com> a �crit dans le message de news: 0fb8b0a6-3dad-41ec-abf8-d91f8020aa18(a)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
From: cbarak on 21 Jan 2010 15:08 On Jan 21, 2:42 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote: > "cbarak" <charlinba...(a)gmail.com> a écrit dans le message de news: > 0fb8b0a6-3dad-41ec-abf8-d91f8020a...(a)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!
From: Gerard H. Pille on 21 Jan 2010 15:43 cbarak wrote: > On Jan 21, 2:42 pm, "Michel Cadot"<micadot{at}altern{dot}org> wrote: >> SQL> select length('rc:jh:nb')-length(replace('rc:jh:nb',':',''))+1 nb from dual; >> NB >> ---------- >> 3 >> >> 1 row selected. >> >> Regards >> Michel > > Thanks Michel! Cette solution vous est apport�e par Michel Cadeau.
From: Vladimir M. Zakharychev on 23 Jan 2010 04:31
On Jan 21, 11:43 pm, "Gerard H. Pille" <g...(a)skynet.be> wrote: > cbarak wrote: > > On Jan 21, 2:42 pm, "Michel Cadot"<micadot{at}altern{dot}org> wrote: > >> SQL> select length('rc:jh:nb')-length(replace('rc:jh:nb',':',''))+1 nb from dual; > >> NB > >> ---------- > >> 3 > > >> 1 row selected. > > >> Regards > >> Michel > > > Thanks Michel! > > Cette solution vous est apportée par Michel Cadeau. Was this a french wordplay? Or are you just giving credit where credit is due? Regards, Vladimir M. Zakharychev N-Networks, makers of Dynamic PSP(tm) http://www.dynamicpsp.com |