# 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)

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?
>
> 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

Original text of this message