character count in a string
Eitan M
how can I get a specific character count in a string
(
i.e : string is 56222, and I am looking for '2' occurance
when i do :
select charcount('56222') should return : 3
)
Thanks :)
gazzag
I would look into building a user-defined function using the INSTR and
SUBSTR built-in functions.
HTH
-g
Anurag Varma
ORA92> select length('56222') - length(replace('56222','2')) from dual;
LENGTH('56222')-LENGTH(REPLACE
------------------------------
3
10GR2> select length(regexp_replace('56222','[^2]','')) from dual;
LENGTH(REGEXP_REPLACE('56222','[^2]',''))
-----------------------------------------
3
Anurag
Charles Hooper
INSTR is all that you need. See:
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/functions068.htm
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
DA Morgan
Given the quality of your responses I am going to have to ask ... how.
I think Anurag's response is one solution and this would be mine.
SELECT LENGTH(TRANSLATE('56222', '2013456789', '2')) FROM dual;
Though I can see numerous creative possibilities using regular
expressions, etc.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Charles Hooper
Sorry, I misread the question and do not have an answer. I thought
that he was looking for the position of the third "2" in a string.
Ignore this:
SELECT
SUM(SIGN(INSTR('562225622256222','2',1,ROWNUM)))
FROM
DUAL
CONNECT BY
LEVEL<20;
SUM(SIGN(INSTR('562225622256222','2',1,ROWNUM)))
-------------
9
Charles Hooper
gazzag suggested SUBSTR, looks like that will work also:
SELECT
SUM(DECODE(SUBSTR('562225622256222',ROWNUM,1),'2',1,0))
FROM
DUAL
CONNECT BY
LEVEL<255;
SUM(DECODE(SUBSTR('562225622256222',ROWNUM,1),'2',1,0))
------------------
Charles Hooper
Two more:
SELECT
SUM(
CASE WHEN INSTR('562225622256222','2',1,ROWNUM)>0 THEN
1
ELSE
0
END
)
FROM
DUAL
CONNECT BY
LEVEL<20;
SELECT
COUNT(
CASE WHEN INSTR('562225622256222','2',1,ROWNUM)>0 THEN
1
ELSE
NULL
END
)
FROM
DUAL
CONNECT BY
LEVEL<20;
SUM(CASEWHENINSTR('562225622256222','2',1,ROWNUM)>0THEN1ELSE0END)
9
COUNT(CASEWHENINSTR('562225622256222','2',1,ROWNUM)>0THEN1ELSENULLEND)
Maxim Demenko
Sorry, could not resist ;-)
SQL> declare
2 s number;
3 c number;
4 begin
5
6 s:=dbms_utility.get_time;
7 for i in 1..100000 loop
8 SELECT SUM(DECODE(SUBSTR('562225622256222',ROWNUM,1),'2',1,0))
9 into c
10 FROM DUAL CONNECT BY LEVEL<=15;
11 end loop;
12 s := dbms_utility.get_time -s;
13 dbms_output.put_line('SUBSTR/DECODE/CONNECT BY time: '||
trunc(s/100));
14
15 s:=dbms_utility.get_time;
16 for i in 1..100000 loop
17 SELECT SUM(SIGN(INSTR('562225622256222','2',1,ROWNUM)))
18 into c
19 FROM DUAL CONNECT BY LEVEL<=15;
20 end loop;
21 s := dbms_utility.get_time -s;
22 dbms_output.put_line('SIGN/INSTR/CONNECT BY time: '||
trunc(s/100));
23
24 s:=dbms_utility.get_time;
25 for i in 1..100000 loop
26 select length('562225622256222') -
length(replace('562225622256222','2'))
27 into c
28 from dual;
29 end loop;
30 s := dbms_utility.get_time -s;
31 dbms_output.put_line('LENGTH/REPLACE time: '|| trunc(s/100));
32
33 s:=dbms_utility.get_time;
34 for i in 1..100000 loop
35 select length(regexp_replace('562225622256222','[^2]',''))
36 into c
37 from dual;
38 end loop;
39 s := dbms_utility.get_time -s;
40 dbms_output.put_line('REGEXP_REPLACE time: '|| trunc(s/100));
41 end;
42 /
SUBSTR/DECODE/CONNECT BY time: 17
SIGN/INSTR/CONNECT BY time: 18
LENGTH/REPLACE time: 13
REGEXP_REPLACE time: 13
PL/SQL procedure successfully completed.
Best regards
Maxim
Charles Hooper
Nice test!
SUBSTR/DECODE/CONNECT BY time: 5
SIGN/INSTR/CONNECT BY time: 5
LENGTH/REPLACE time: 1
REGEXP_REPLACE time: 1
The above test results are from Oracle 10.2.0.2 running on Windows 2003
x64
Inefficiency of SQL is not a problem, it just means that you need a
bigger CPU :-)
Actually, the test proves a valid point - just because it works, does
not mean that it should be used. Thanks for taking the time to build
the test.
DA Morgan
You have far too much time on your hands. Can I get you a glass of scotch?
DA Morgan
If you have even a tinge of envy I suggest responding with:
1. Write it out to a file using UTL_FILE
2. Read it back in using DBMS_SCHEDULER
3. Creating it as a wrapped package using DBMS_DDL
4. Executing it using DBMS_SQL.
Use the PL/SQL data types POSITIVEN and NATURALN to confuse almost
everyone as to your actual intent. ;-)
Maxim Demenko
> Maxim Demenko wrote:
>> Charles Hooper schrieb:
Wrong!!! i simple have a vi macro, that reformat a usenet posting into
an sql script ;-). The really bad guy is not me, that is Charles !
Can I get you a glass of scotch?
a glass as russian used to define it (250cl) ?
Best regards
Maxim
Maxim Demenko
>
> Inefficiency of SQL is not a problem, it just means that you need a
> bigger CPU :-)
>
> Actually, the test proves a valid point - just because it works, does
> not mean that it should be used. Thanks for taking the time to build
> the test.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
>
I just had a feeling, you would do it otherwise ;-)
Seriously, the time increase massively with increasing of connect by
levels ( that of course intuitively seems to be correct), another nice
thing - regexp is pretty fast (i thought initially, replace will be much
faster).
Best regards
Maxim