Re: Counting Strings
Date: Thu, 10 Jul 2008 10:20:57 -0700 (PDT)
Message-ID: <33ae48f6-bc3f-4126-b9df-c0bd3fc1d16e@p25g2000hsf.googlegroups.com>
On Jul 10, 9:02 am, Mtek <m..._at_mtekusa.com> wrote:
> On Jul 10, 7:37 am, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
>
>
>
>
>
> > On Jul 9, 3:05 pm, Anurag Varma <avora..._at_gmail.com> wrote:
>
> > > On Jul 9, 3:16 pm, Mtek <m..._at_mtekusa.com> wrote:
>
> > > > On Jul 9, 1:38 pm, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
>
> > > > > Comments embedded.
>
> > > > > On Jul 9, 1:00 pm, Mtek <m..._at_mtekusa.com> wrote:
>
> > > > > > Hi,
>
> > > > > > We are running 10g r2. Too bad REGEXP_COUNT does not work. I need to
> > > > > > count the occurances of a string within a string.
>
> > > > > > It works from when used in a select from a table:
>
> > > > > > SELECT LENGTH(c1) - LENGTH(REPLACE(c1, 'Oracle', 'Oracl')) FROM x;
>
> > > > > It works anyway, no matter how you code it. Of course in your first
> > > > > example you're making the string shorter by 4 characters:
>
> > > > > SQL> create table x(c1 varchar2(25));
>
> > > > > Table created.
>
> > > > > SQL>
> > > > > SQL> insert into x values ('OracleOracleOracleOracle');
>
> > > > > 1 row created.
>
> > > > > SQL>
> > > > > SQL> commit;
>
> > > > > Commit complete.
>
> > > > > SQL>
> > > > > SQL> SELECT LENGTH(c1) - LENGTH(REPLACE(c1, 'Oracle', 'Oracl')) FROM
> > > > > x;
>
> > > > > LENGTH(C1)-LENGTH(REPLACE(C1,'ORACLE','ORACL'))
> > > > > -----------------------------------------------
> > > > > 4
>
> > > > > SQL>
>
> > > > > > This gives me a result of 4, which is correct. However, what if in PL/
> > > > > > SQL I need to use it against a variable?
>
> > > > > > SELECT LENGTH('ABC:DEF:GAB:CDE:GS') -
> > > > > > LENGTH(REPLACE('ABC:DEF:GAB:CDE:GS', ':', 'art')) FROM dual;
>
> > > > > > I get -8????
>
> > > > > Because you've made the string LONGER by 8 characters, which should
> > > > > have been obvious:
>
> > > > > SQL> SELECT LENGTH('ABC:DEF:GAB:CDE:GS') from dual;
>
> > > > > LENGTH('ABC:DEF:GAB:CDE:GS')
> > > > > ----------------------------
> > > > > 18
>
> > > > > SQL>
> > > > > SQL> SELECT
> > > > > 2 LENGTH(REPLACE('ABC:DEF:GAB:CDE:GS', ':', 'art')) FROM dual;
>
> > > > > LENGTH(REPLACE('ABC:DEF:GAB:CDE:GS',':','ART'))
> > > > > -----------------------------------------------
> > > > > 26
>
> > > > > SQL>
> > > > > SQL> SELECT LENGTH('ABC:DEF:GAB:CDE:GS') -
> > > > > LENGTH(REPLACE('ABC:DEF:GAB:CDE:GS', ':', 'art')) FROM dual;
>
> > > > > LENGTH('ABC:DEF:GAB:CDE:GS')-
> > > > > LENGTH(REPLACE('ABC:DEF:GAB:CDE:GS',':','ART'))
> > > > > ----------------------------------------------------------------------------
>
> > > > > -8
>
> > > > > SQL>
>
> > > > > Reverse the strings and you'll get a positive value, or use ABS() to
> > > > > return the difference:
>
> > > > > SQL> SELECT ABS(LENGTH('ABC:DEF:GAB:CDE:GS') -
> > > > > LENGTH(REPLACE('ABC:DEF:GAB:CDE:GS', ':', 'art'))) FROM dual;
>
> > > > > ABS(LENGTH('ABC:DEF:GAB:CDE:GS')-
> > > > > LENGTH(REPLACE('ABC:DEF:GAB:CDE:GS',':','ART'))
> > > > > --------------------------------------------------------------------------------
>
> > > > > 8
>
> > > > > SQL>
>
> > > > > > Anyone?
>
> > > > > > Thank you,
>
> > > > > > John.
>
> > > > > David Fitzjarrell
>
> > > > David,
>
> > > > Well, maybe I'm being an idiot, but I want to count the number of
> > > > occurences of a particular string. So, in the above, the colon is
> > > > present 4 times. Why do I get 8?
>
> > > Why are you replacing ':' with 'art'??? Replace it with nothing to
> > > get the answer:
>
> > > SELECT LENGTH('ABC:DEF:GAB:CDE:GS') -
> > > LENGTH(REPLACE('ABC:DEF:GAB:CDE:GS', ':')) FROM dual;- Hide quoted text -
>
> > > - Show quoted text -
>
> > Ahh, that's the simple way. :) He should create a function to do
> > this:
>
> > SQL> create or replace function f_ct_substr(p_strg IN varchar2, p_srch
> > IN varchar2)
> > 2 return number
> > 3 is
> > 4 v_occur number:=0;
> > 5 v_start number:=1;
> > 6 v_pos number:=1;
> > 7
> > 8 begin
> > 9 while v_pos > 0
> > 10 loop
> > 11 v_pos := instr(p_strg, p_srch, v_start);
> > 12 if v_pos > 0 then
> > 13 v_occur := v_occur + 1;
> > 14 v_start := v_pos + 1;
> > 15 end if;
> > 16 end loop;
> > 17 return v_occur;
> > 18 end;
> > 19 /
>
> > Function created.
>
> > SQL>
> > SQL> select f_ct_substr('ABC:DEF:GAB:CDE:GS',':') from dual;
>
> > F_CT_SUBSTR('ABC:DEF:GAB:CDE:GS',':')
> > -------------------------------------
> > 4
>
> > SQL> select f_ct_substr(':ABC:DEF:GAB:CDE::GS',':') from dual;
>
> > F_CT_SUBSTR(':ABC:DEF:GAB:CDE::GS',':')
> > ---------------------------------------
> > 6
>
> > SQL> select f_ct_substr('ABC:DEF::GAB:CDE:GS',':') from dual;
>
> > F_CT_SUBSTR('ABC:DEF::GAB:CDE:GS',':')
> > --------------------------------------
> > 5
>
> > SQL> select f_ct_substr('ABC:DEF:GAB:CDE:GS:',':') from dual;
>
> > F_CT_SUBSTR('ABC:DEF:GAB:CDE:GS:',':')
> > --------------------------------------
> > 5
>
> > SQL> select f_ct_substr('ABC::DEF:GAB:CDE:GS',':') from dual;
>
> > F_CT_SUBSTR('ABC::DEF:GAB:CDE:GS',':')
> > --------------------------------------
> > 5
>
> > SQL> select f_ct_substr('ABC::::DEF:GAB:CDE:GS',':') from dual;
>
> > F_CT_SUBSTR('ABC::::DEF:GAB:CDE:GS',':')
> > ----------------------------------------
> > 7
>
> > SQL> select f_ct_substr('ABC:DEF:::GAB:CDE:GS',':') from dual;
>
> > F_CT_SUBSTR('ABC:DEF:::GAB:CDE:GS',':')
> > ---------------------------------------
> > 6
>
> > SQL> select f_ct_substr('ABC:DEF:GAB::::::CDE:GS',':') from dual;
>
> > F_CT_SUBSTR('ABC:DEF:GAB::::::CDE:GS',':')
> > ------------------------------------------
> > 9
>
> > SQL>
>
> > David Fitzjarrell- Hide quoted text -
>
> > - Show quoted text -
>
> SELECT LENGTH('ABC:DEF:GAB:CDE:GS::') -
> LENGTH(REPLACE('ABC:DEF:GAB:CDE:GS', ':')) FROM dual;
> SELECT (LENGTH('ABC:DEF:GAB:CDE:GS::') -
> LENGTH(REGEXP_REPLACE('ABC:DEF:GAB:CDE:GS::',':')) )/LENGTH(':')
> noOccurances FROM DUAL;
>
> They both work! Thanks everyone!!
>
> BTW: I replaced it with 'art' just to have something to replace it
> with. I was unaware that this was such a factor in finding the
> count.....
>
> John- Hide quoted text -
>
> - Show quoted text -
As I said earlier LENGTH doesn't count anything except the total number of characters in the string. And as I said before since you lengthened the string by 8 characters with your substitution it's no wonder you were generating the results you reported.
David Fitzjarrell Received on Thu Jul 10 2008 - 12:20:57 CDT