Re: String function

From: Eric Parker <eric.parkerthedross_at_virgin.net>
Date: Tue, 26 Aug 2003 16:42:35 +0100
Message-ID: <NdL2b.3951$MS5.68631@newsfep4-glfd.server.ntli.net>

"Max" <massimo.panero_at_ideainformatica.org> wrote in message news:bifarn$8hvkb$1_at_ID-121437.news.uni-berlin.de...
> Is there a function that return how many occurrence of a substring are in
a
> string?
> Or how can i do by myself?
> Example:
> ST := 'THIS IS A STRING';
> N := XFunction ( ST, 'S' ); // return 3
> N := XFunction ( ST, 'IS' ); // return 2
> N := XFunction ( ST, 'X' ); // return 0
>
>

Max

This may help you.

SELECT
NVL (
  LENGTH (
    TRANSLATE (

      REPLACE ('THIS IS A STRING', 'IS', '|'),
        '|ABCDEFGHIJKLMNOPQRSTUVWXYZ ', '|')), 0) THE_COUNT

FROM DUAL; This assumes all text is uppercase or spaces without any pipe characters.

eric

--
Remove the dross to contact me directly

Received on Tue Aug 26 2003 - 10:42:35 CDT