Hello, 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 )
Eitan M wrote: > Hello, > 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 :)
I would look into building a user-defined function using the INSTR and SUBSTR built-in functions.
Eitan M wrote: > Hello, > 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 :)
ORA92> select length('56222') - length(replace('56222','2')) from dual;
Eitan M wrote: > Hello, > 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 > )
Charles Hooper wrote: > Eitan M wrote: >> Hello, >> 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 >> )
> Charles Hooper > PC Support Specialist > K&M Machine-Fabricating, Inc.
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 damor...@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org
DA Morgan wrote: > Charles Hooper wrote: > > Eitan M wrote: > >> Hello, > >> 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 > >> )
> > Charles Hooper > > PC Support Specialist > > K&M Machine-Fabricating, Inc.
> 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 > damor...@x.washington.edu > (replace x with u to respond) > Puget Sound Oracle Users Group > www.psoug.org
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;
Charles Hooper wrote: > DA Morgan wrote: > > Charles Hooper wrote: > > > Eitan M wrote: > > >> Hello, > > >> 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 > > >> )
> > > Charles Hooper > > > PC Support Specialist > > > K&M Machine-Fabricating, Inc.
> > 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 > > damor...@x.washington.edu > > (replace x with u to respond) > > Puget Sound Oracle Users Group > > www.psoug.org
> 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;
> Charles Hooper > PC Support Specialist > K&M Machine-Fabricating, Inc.
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;
Charles Hooper wrote: > DA Morgan wrote: > > Charles Hooper wrote: > > > Eitan M wrote: > > >> Hello, > > >> 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 > > >> )
> > > Charles Hooper > > > PC Support Specialist > > > K&M Machine-Fabricating, Inc.
> > 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 > > damor...@x.washington.edu > > (replace x with u to respond) > > Puget Sound Oracle Users Group > > www.psoug.org
> 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;
> Charles Hooper wrote: >> DA Morgan wrote: >>> Charles Hooper wrote: >>>> Eitan M wrote: >>>>> Hello, >>>>> 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 >>>>> )
>>>> Charles Hooper >>>> PC Support Specialist >>>> K&M Machine-Fabricating, Inc. >>> 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 >>> damor...@x.washington.edu >>> (replace x with u to respond) >>> Puget Sound Oracle Users Group >>> www.psoug.org >> 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;
>> Charles Hooper >> PC Support Specialist >> K&M Machine-Fabricating, Inc.
> 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;
> Charles Hooper > PC Support Specialist > K&M Machine-Fabricating, Inc.
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
Maxim Demenko wrote: > Charles Hooper schrieb: >> Charles Hooper wrote: >>> DA Morgan wrote: >>>> Charles Hooper wrote: >>>>> Eitan M wrote: >>>>>> Hello, >>>>>> 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 >>>>>> )
>>>>> Charles Hooper >>>>> PC Support Specialist >>>>> K&M Machine-Fabricating, Inc. >>>> 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 >>>> damor...@x.washington.edu >>>> (replace x with u to respond) >>>> Puget Sound Oracle Users Group >>>> www.psoug.org >>> 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;
>>> Charles Hooper >>> PC Support Specialist >>> K&M Machine-Fabricating, Inc.
>> 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;
>> Charles Hooper >> PC Support Specialist >> K&M Machine-Fabricating, Inc.
> 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
You have far too much time on your hands. Can I get you a glass of scotch? -- Daniel A. Morgan University of Washington damor...@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org
Charles Hooper wrote: > Maxim Demenko wrote: >> 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
> 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.
> Charles Hooper > PC Support Specialist > K&M Machine-Fabricating, Inc.
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. ;-) -- Daniel A. Morgan University of Washington damor...@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org
> Maxim Demenko wrote: >> Charles Hooper schrieb: > You have far too much time on your hands.
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) ?
> 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).
