Re: Counting Strings
Date: Thu, 10 Jul 2008 06:25:55 -0700 (PDT)
On Jul 9, 7:00 pm, Mtek <m..._at_mtekusa.com> wrote:
> 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????
> Thank you,
> 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;
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
__/__/__/ Tessella Support Services plc __/__/__/ 3 Vineyard Chambers, ABINGDON, OX14 3PX, England __/__/__/ Tel: (44)(0)1235-555511 Fax: (44)(0)1235-553301www.tessella.com Registered in England No. 1466429 Received on Thu Jul 10 2008 - 08:25:55 CDT