Re: Looking for a SQL Function

From: Michael O'Shea <mdo_remove_at_strychnine.co.uk>
Date: 6 Nov 2005 04:19:53 -0800
Message-ID: <1131279593.258295.188120@g47g2000cwa.googlegroups.com>

To extend to the contributions in this thread so far, if the requirement were that the substring was a regex rather than a string literal, then the following snippet might prove useful.

The example demonstrates how to count the number of occurrences of a substring (in this case two consecutive letters between A and M followed by a digit) in SQL.

Oracle 10g regular expression extensions are discussed here: http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200.pdf

Regards
Mike

Michael D O'Shea
http://www.strychnine.co.uk

SQL>
SQL>
SQL> SELECT COUNT(*) freq

  2 FROM DUAL
  3 CONNECT BY
REGEXP_INSTR('ABAB123A123B123AB*A123AZ1AC1','[A-M]{2}[0-9]',1,ROWNUM)>0;       FREQ

SQL>
SQL> SELECT *
2 FROM V$VERSION; BANNER

Personal Oracle Database 10g Release 10.1.0.2.0 - Production PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production NLSRTL Version 10.1.0.2.0 - Production

SQL> R. P. wrote:
> Is there an Oracle character function returning the number of occurances
> of a substring in a string? INSTR() is close but not quite what I am
> looking for.
>
> Thanks,
> Rudy
Received on Sun Nov 06 2005 - 06:19:53 CST