Re: Counting Strings

From: Michael D O'Shea <michael.oshea_at_tessella.com>
Date: Thu, 10 Jul 2008 06:25:55 -0700 (PDT)
Message-ID: <422d5383-e6e2-4d37-9fdc-8543276722a1@a1g2000hsb.googlegroups.com>

On Jul 9, 7: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;
>
> 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????
>
> Anyone?
>
> Thank you,
>
> John.

Hello John,

> We are running 10g r2. Too bad REGEXP_COUNT does not
> work. I need to count the occurances of a string within
> a string.

REGEXP_COUNT is 11g

REGEXP_REPLACE however is implemented in 10.2, your target DBMS version. You could use it as follows (the minimal example below uses hard coded strings 'Oracle' etc so replace with your arguments/table columns). This example has the advantage that you can determine the number of occurances of a string within a string based on regex/ wildcards etc and not just string literals.

SQL>
SQL>
SQL> SELECT (
  2                   LENGTH('OracleOracleCorporationOracleMetaLink')
  3                  -
LENGTH(REGEXP_REPLACE('OracleOracleCorporationOracleMetaLink','Oracle'))
  4         )/LENGTH('Oracle') noOccurances
  5            FROM DUAL;

NOOCCURANCES

---

           3

SQL>
SQL>
SQL> SELECT *

  2 FROM V$VERSION; BANNER

---

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production

SQL> Regards

Mike

TESSELLA Michael.OShea_at_tessella.com

__/__/__/  Tessella Support Services plc
__/__/__/  3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/  Tel: (44)(0)1235-555511  Fax: (44)(0)1235-553301

www.tessella.com Registered in England No. 1466429 Received on Thu Jul 10 2008 - 08:25:55 CDT