Re: Counting Strings

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 Received on Wed Jul 09 2008 - 13:38:58 CDT