Counting Strings
Mtek
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????
Anyone?
Thank you,
John.
ora...@msn.com
On Jul 9, 1:00 pm, Mtek <m...@mtekusa.com> wrote:
> Hi,
>
> 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;
>
It works anyway, no matter how you code it. Of course in your first
example you're making the string shorter by 4 characters:
SQL> create table x(c1 varchar2(25));
Table created.
SQL>
SQL> insert into x values ('OracleOracleOracleOracle');
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> SELECT LENGTH(c1) - LENGTH(REPLACE(c1, 'Oracle', 'Oracl')) FROM
x;
LENGTH(C1)-LENGTH(REPLACE(C1,'ORACLE','ORACL'))
-----------------------------------------------
4
SQL>
> 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????
>
Because you've made the string LONGER by 8 characters, which should
have been obvious:
SQL> SELECT LENGTH('ABC:DEF:GAB:CDE:GS') from dual;
LENGTH('ABC:DEF:GAB:CDE:GS')
----------------------------
18
SQL>
SQL> SELECT
2 LENGTH(REPLACE('ABC:DEF:GAB:CDE:GS', ':', 'art')) FROM dual;
LENGTH(REPLACE('ABC:DEF:GAB:CDE:GS',':','ART'))
-----------------------------------------------
26
SQL>
SQL> SELECT LENGTH('ABC:DEF:GAB:CDE:GS') -
LENGTH(REPLACE('ABC:DEF:GAB:CDE:GS', ':', 'art')) FROM dual;
LENGTH('ABC:DEF:GAB:CDE:GS')-
LENGTH(REPLACE('ABC:DEF:GAB:CDE:GS',':','ART'))
----------------------------------------------------------------------------
-8
SQL>
Reverse the strings and you'll get a positive value, or use ABS() to
return the difference:
SQL> SELECT ABS(LENGTH('ABC:DEF:GAB:CDE:GS') -
LENGTH(REPLACE('ABC:DEF:GAB:CDE:GS', ':', 'art'))) FROM dual;
ABS(LENGTH('ABC:DEF:GAB:CDE:GS')-
LENGTH(REPLACE('ABC:DEF:GAB:CDE:GS',':','ART'))
--------------------------------------------------------------------------------
8
SQL>
> Anyone?
>
> Thank you,
>
> John.
David Fitzjarrell
Mtek
>
> -8
>
> SQL>
>
> Reverse the strings and you'll get a positive value, or use ABS() to
> return the difference:
>
> SQL> SELECT ABS(LENGTH('ABC:DEF:GAB:CDE:GS') -
> LENGTH(REPLACE('ABC:DEF:GAB:CDE:GS', ':', 'art'))) FROM dual;
>
> ABS(LENGTH('ABC:DEF:GAB:CDE:GS')-
> LENGTH(REPLACE('ABC:DEF:GAB:CDE:GS',':','ART'))
>
> 8
>
> SQL>
>
> > Anyone?
>
> > Thank you,
>
> > John.
>
> David Fitzjarrell
David,
Well, maybe I'm being an idiot, but I want to count the number of
occurences of a particular string. So, in the above, the colon is
present 4 times. Why do I get 8?
ora...@msn.com
>
> - Show quoted text -
LENGTH() counts nothing, it reports the overall length of the string.
In the first case you reduced the overall length of the string by 4
characters (1 'e' for each occurrence of 'Oracle', of which there were
4). In the second case you've replaced the ':' with 'art' and, in so
doing, INCREASED the length of the string by 8 characters, thus your
difference. You can, in PL/SQL, 'walk' the string and keep a running
counter of each occurrence of the character or substring you're
interested in finding, but using LENGTH() won't generate any sort of a
count. You should have seen that from my initial response, where I
returned the LENGTH() of the first string, the LENGTH() of the second
string and the difference as you've written it.
You need to re-think this 'solution'.
David Fitzjarrell
Anurag Varma
Why are you replacing ':' with 'art'??? Replace it with nothing to
get the answer:
SELECT LENGTH('ABC:DEF:GAB:CDE:GS') -
LENGTH(REPLACE('ABC:DEF:GAB:CDE:GS', ':')) FROM dual;
ora...@msn.com
>
> - Show quoted text -
Ahh, that's the simple way. :) He should create a function to do
this:
SQL> create or replace function f_ct_substr(p_strg IN varchar2, p_srch
IN varchar2)
2 return number
3 is
4 v_occur number:=0;
5 v_start number:=1;
6 v_pos number:=1;
7
8 begin
9 while v_pos > 0
10 loop
11 v_pos := instr(p_strg, p_srch, v_start);
12 if v_pos > 0 then
13 v_occur := v_occur + 1;
14 v_start := v_pos + 1;
15 end if;
16 end loop;
17 return v_occur;
18 end;
19 /
Function created.
SQL>
SQL> select f_ct_substr('ABC:DEF:GAB:CDE:GS',':') from dual;
F_CT_SUBSTR('ABC:DEF:GAB:CDE:GS',':')
-------------------------------------
4
SQL> select f_ct_substr(':ABC:DEF:GAB:CDE::GS',':') from dual;
F_CT_SUBSTR(':ABC:DEF:GAB:CDE::GS',':')
---------------------------------------
6
SQL> select f_ct_substr('ABC:DEF::GAB:CDE:GS',':') from dual;
F_CT_SUBSTR('ABC:DEF::GAB:CDE:GS',':')
--------------------------------------
5
SQL> select f_ct_substr('ABC:DEF:GAB:CDE:GS:',':') from dual;
F_CT_SUBSTR('ABC:DEF:GAB:CDE:GS:',':')
--------------------------------------
5
SQL> select f_ct_substr('ABC::DEF:GAB:CDE:GS',':') from dual;
F_CT_SUBSTR('ABC::DEF:GAB:CDE:GS',':')
--------------------------------------
5
SQL> select f_ct_substr('ABC::::DEF:GAB:CDE:GS',':') from dual;
F_CT_SUBSTR('ABC::::DEF:GAB:CDE:GS',':')
----------------------------------------
7
SQL> select f_ct_substr('ABC:DEF:::GAB:CDE:GS',':') from dual;
F_CT_SUBSTR('ABC:DEF:::GAB:CDE:GS',':')
---------------------------------------
6
SQL> select f_ct_substr('ABC:DEF:GAB::::::CDE:GS',':') from dual;
F_CT_SUBSTR('ABC:DEF:GAB::::::CDE:GS',':')
------------------------------------------
9
SQL>
David Fitzjarrell
Michael D O'Shea
Hello John,
> 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;
NOOCCURANCES
------------
3
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
SQL>
Regards
Mike
TESSELLA Michae...@tessella.com
__/__/__/ Tessella Support Services plc
__/__/__/ 3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/ Tel: (44)(0)1235-555511 Fax: (44)(0)1235-553301
www.tessella.com Registered in England No. 1466429
Mtek
>
> - Show quoted text -
SELECT LENGTH('ABC:DEF:GAB:CDE:GS::') -
LENGTH(REPLACE('ABC:DEF:GAB:CDE:GS', ':')) FROM dual;
SELECT (LENGTH('ABC:DEF:GAB:CDE:GS::') -
LENGTH(REGEXP_REPLACE('ABC:DEF:GAB:CDE:GS::',':')) )/LENGTH(':')
noOccurances FROM DUAL;
They both work! Thanks everyone!!
BTW: I replaced it with 'art' just to have something to replace it
with. I was unaware that this was such a factor in finding the
count.....
John
ora...@msn.com
>
> - Show quoted text -
As I said earlier LENGTH doesn't count anything except the total
number of characters in the string. And as I said before since you
lengthened the string by 8 characters with your substitution it's no
wonder you were generating the results you reported.
David Fitzjarrell