Re: Counting Strings

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; Received on Wed Jul 09 2008 - 15:05:24 CDT