Re: find out how many times particular character occur in given string.

From: ddf <oratune_at_msn.com>
Date: Wed, 10 Dec 2008 07:20:45 -0800 (PST)
Message-ID: <ea403f4c-9002-460c-8811-e0358a05348d@w1g2000prm.googlegroups.com>

On Dec 10, 7:19 am, Sanjeev <sanjeev.atvan..._at_gmail.com> wrote:
> Dear Gurus,
>
>   I want to find out how many times particular character occur in
> given string.
>   e.g. 'A*B*C*D*E*F' count of '*' is 5
>          'A*B*C*D' count of '*' is 3
>          'A' count of '*' is 0
>
> Is there any function available in SQL to find out the above
> requirement ?
>
> Could anyone help me in above?
>
> Thanking in advance
> Sanjeev

This works:

SQL>
SQL> @token_count_ex A*B*C*D*E *
SQL> select count(*) - 1 occurrences

  2 from
  3 (SELECT SUBSTR( '&&1' || '&&2',

  4  		     NVL(LAG(TOKEN) OVER (ORDER BY TOKEN),0) + 1,
  5  		     TOKEN - (NVL(LAG(TOKEN) OVER (ORDER BY TOKEN),0) + 1))

  6 FROM (
  7 SELECT LEVEL,

  8  	     INSTR( '&&1' || '&&2', '&&2', 1, LEVEL ) TOKEN
  9  	FROM DUAL
 10   CONNECT BY
 11  	 INSTR( '&&1' || '&&2', '&&2', 1, LEVEL ) != 0
 12   ORDER BY
 13  	 LEVEL
 14  	 ))

 15 /

OCCURRENCES

---

          4

SQL>
SQL> undefine 1
SQL> undefine 2
SQL>
SQL> @token_count_ex A*B*C*D *
SQL> select count(*) - 1 occurrences

  2 from
  3 (SELECT SUBSTR( '&&1' || '&&2',

  4  		     NVL(LAG(TOKEN) OVER (ORDER BY TOKEN),0) + 1,
  5  		     TOKEN - (NVL(LAG(TOKEN) OVER (ORDER BY TOKEN),0) + 1))

  6 FROM (
  7 SELECT LEVEL,

  8  	     INSTR( '&&1' || '&&2', '&&2', 1, LEVEL ) TOKEN
  9  	FROM DUAL
 10   CONNECT BY
 11  	 INSTR( '&&1' || '&&2', '&&2', 1, LEVEL ) != 0
 12   ORDER BY
 13  	 LEVEL
 14  	 ))

 15 /

OCCURRENCES

---

          3

SQL>
SQL> undefine 1
SQL> undefine 2
SQL>
SQL> @token_count_ex A*B *
SQL> select count(*) - 1 occurrences

  2 from
  3 (SELECT SUBSTR( '&&1' || '&&2',

  4  		     NVL(LAG(TOKEN) OVER (ORDER BY TOKEN),0) + 1,
  5  		     TOKEN - (NVL(LAG(TOKEN) OVER (ORDER BY TOKEN),0) + 1))

  6 FROM (
  7 SELECT LEVEL,

  8  	     INSTR( '&&1' || '&&2', '&&2', 1, LEVEL ) TOKEN
  9  	FROM DUAL
 10   CONNECT BY
 11  	 INSTR( '&&1' || '&&2', '&&2', 1, LEVEL ) != 0
 12   ORDER BY
 13  	 LEVEL
 14  	 ))

 15 /

OCCURRENCES

---

          1

SQL>
SQL> undefine 1
SQL> undefine 2
SQL>
SQL> @token_count_ex A*B*C*D*E*F *
SQL> select count(*) - 1 occurrences

  2 from
  3 (SELECT SUBSTR( '&&1' || '&&2',

  4  		     NVL(LAG(TOKEN) OVER (ORDER BY TOKEN),0) + 1,
  5  		     TOKEN - (NVL(LAG(TOKEN) OVER (ORDER BY TOKEN),0) + 1))

  6 FROM (
  7 SELECT LEVEL,

  8  	     INSTR( '&&1' || '&&2', '&&2', 1, LEVEL ) TOKEN
  9  	FROM DUAL
 10   CONNECT BY
 11  	 INSTR( '&&1' || '&&2', '&&2', 1, LEVEL ) != 0
 12   ORDER BY
 13  	 LEVEL
 14  	 ))

 15 /

OCCURRENCES

---

          5

SQL>
SQL> undefine 1
SQL> undefine 2
SQL>
SQL> @token_count_ex A *
SQL> select count(*) - 1 occurrences

  2 from
  3 (SELECT SUBSTR( '&&1' || '&&2',

  4  		     NVL(LAG(TOKEN) OVER (ORDER BY TOKEN),0) + 1,
  5  		     TOKEN - (NVL(LAG(TOKEN) OVER (ORDER BY TOKEN),0) + 1))

  6 FROM (
  7 SELECT LEVEL,

  8  	     INSTR( '&&1' || '&&2', '&&2', 1, LEVEL ) TOKEN
  9  	FROM DUAL
 10   CONNECT BY
 11  	 INSTR( '&&1' || '&&2', '&&2', 1, LEVEL ) != 0
 12   ORDER BY
 13  	 LEVEL
 14  	 ))

 15 /

OCCURRENCES

---

          0

SQL>
SQL> undefine 1
SQL> undefine 2
SQL>
SQL> @token_count_ex A*B*C*D*E*F*G*H*I*J *
SQL> select count(*) - 1 occurrences

  2 from
  3 (SELECT SUBSTR( '&&1' || '&&2',

  4  		     NVL(LAG(TOKEN) OVER (ORDER BY TOKEN),0) + 1,
  5  		     TOKEN - (NVL(LAG(TOKEN) OVER (ORDER BY TOKEN),0) + 1))

  6 FROM (
  7 SELECT LEVEL,

  8  	     INSTR( '&&1' || '&&2', '&&2', 1, LEVEL ) TOKEN
  9  	FROM DUAL
 10   CONNECT BY
 11  	 INSTR( '&&1' || '&&2', '&&2', 1, LEVEL ) != 0
 12   ORDER BY
 13  	 LEVEL
 14  	 ))

 15 /

OCCURRENCES

---

          9

SQL>
SQL> undefine 1
SQL> undefine 2
SQL>
SQL> @token_count_ex A*B*C *
SQL> select count(*) - 1 occurrences

  2 from
  3 (SELECT SUBSTR( '&&1' || '&&2',

  4  		     NVL(LAG(TOKEN) OVER (ORDER BY TOKEN),0) + 1,
  5  		     TOKEN - (NVL(LAG(TOKEN) OVER (ORDER BY TOKEN),0) + 1))

  6 FROM (
  7 SELECT LEVEL,

  8  	     INSTR( '&&1' || '&&2', '&&2', 1, LEVEL ) TOKEN
  9  	FROM DUAL
 10   CONNECT BY
 11  	 INSTR( '&&1' || '&&2', '&&2', 1, LEVEL ) != 0
 12   ORDER BY
 13  	 LEVEL
 14  	 ))

 15 /

OCCURRENCES

---

          2

SQL>
SQL> undefine 1
SQL> undefine 2
SQL>
SQL> @token_count_ex A*B*C*D*E*F*G*H*I*J*K*L*M*N *
SQL> select count(*) - 1 occurrences

  2 from
  3 (SELECT SUBSTR( '&&1' || '&&2',

  4  		     NVL(LAG(TOKEN) OVER (ORDER BY TOKEN),0) + 1,
  5  		     TOKEN - (NVL(LAG(TOKEN) OVER (ORDER BY TOKEN),0) + 1))

  6 FROM (
  7 SELECT LEVEL,

  8  	     INSTR( '&&1' || '&&2', '&&2', 1, LEVEL ) TOKEN
  9  	FROM DUAL
 10   CONNECT BY
 11  	 INSTR( '&&1' || '&&2', '&&2', 1, LEVEL ) != 0
 12   ORDER BY
 13  	 LEVEL
 14  	 ))

 15 /

OCCURRENCES

---

         13

SQL>
SQL> undefine 1
SQL> undefine 2
SQL>

I will leave it to you to 'massage' this into a function.

David Fitzjarrell Received on Wed Dec 10 2008 - 09:20:45 CST