Very good
A reader, September 27, 2001 - 10:33 am UTC
no of occurences of a string within another string
Melwin Nunes, September 27, 2001 - 11:00 am UTC
CREATE OR REPLACE FUNCTION countm( pi_string IN VARCHAR2,
pi_search IN VARCHAR2,
pi_case_sensitive IN VARCHAR2 DEFAULT 'S')
RETURN NUMBER IS
vn_count NUMBER := 0;
vn_exit NUMBER := 0;
vn_length NUMBER := LENGTH( pi_string);
vv_string VARCHAR2(32000) := pi_string;
vv_search VARCHAR2(32000) := pi_search;
BEGIN
IF UPPER( pi_case_sensitive) = 'I' THEN
vv_search := UPPER( vv_search);
vv_string := UPPER( vv_string);
END IF;
FOR length_rec IN 1..vn_length
LOOP
vn_exit := vn_exit + 1;
vn_exit := INSTR( vv_string, vv_search, vn_exit );
EXIT WHEN vn_exit = 0;
vn_count := vn_count + 1;
END LOOP;
RETURN vn_count;
END countm;
Syntax
COUNTM('char1','char2', 'char3')
Purpose
COUNTM searches char1 beginning with its 1st character for the number of occurrences of char2 and returns the total count. Specifying 'I' for char3 makes the search case insensitive, default is case sensitive. If the search is unsuccessful (if char2 does not appear m times after the nth character of char1) the return value is 0.
Example 1
SELECT COUNTM( 'CORPORATE FLOOR', 'OR')
"Countm" FROM DUAL;
Countm
-------
3
Example 2
SELECT COUNTM( 'CORPORATE FLOOR', 'or')
"Case Sensitive"
FROM DUAL;
Case Sensitive
--------------
0
Example 3
SELECT COUNTM( 'CORPORATE FLOOR', 'or', 'I')
"Case InSensitive"
FROM DUAL;
Case Sensitive
--------------
3
September 27, 2001 - 2:32 pm UTC
Yes, you could do this HOWEVER, just consider the following:
ops$tkyte@ORA8I.WORLD> create table t as select object_name from all_objects;
Table created.
ops$tkyte@ORA8I.WORLD> set timing on
ops$tkyte@ORA8I.WORLD> select max( countm( object_name, 'A' ) ) from t
2 /
MAX(COUNTM(OBJECT_NAME,'A'))
----------------------------
5
<b>Elapsed: 00:00:04.77</b>
ops$tkyte@ORA8I.WORLD> /
MAX(COUNTM(OBJECT_NAME,'A'))
----------------------------
5
<b>Elapsed: 00:00:04.17</b>
ops$tkyte@ORA8I.WORLD> select max( (length(object_name) - length(replace(object_name,'A','')))/length('A') ) from t
2 /
MAX((LENGTH(OBJECT_NAME)-LENGTH(REPLACE(OBJECT_NAME,'A','')))/LENGTH('A'))
--------------------------------------------------------------------------
5
<b>Elapsed: 00:00:00.25</b>
ops$tkyte@ORA8I.WORLD> /
MAX((LENGTH(OBJECT_NAME)-LENGTH(REPLACE(OBJECT_NAME,'A','')))/LENGTH('A'))
--------------------------------------------------------------------------
5
<b>Elapsed: 00:00:00.24</b>
I'll stick with SQL for this one thanks -- anytime you CAN do it in SQL, do it. Using PLSQL, while very very flexible, can increase overhead.
Hmmmmmmmmmmm
Connor, October 01, 2001 - 5:55 pm UTC
There is always some prat who picks holes in things...Me in this case :-)
exec :main_str := 'abc.efg.hij.klm';
exec :srch_str := :main_str;
select (length(:main_str) - length(replace(:main_str,:srch_str,'')))/
length(:srch_str) cnt
from dual;
October 01, 2001 - 7:26 pm UTC
grrrrr
missing nvl:
select (length(:main_str) - nvl(length(replace(:main_str,:srch_str,'')),0))/
length(:srch_str) cnt
from dual;
Search and replace
A reader, October 20, 2003 - 9:16 pm UTC
Tom,
Assume that I have a variable 'test/one 7/8 test/2'. I need to replace '/' with ',' only when the preceding or the previous character is not number. I am expecting the result should be 'test,one 7/8 test/2'. Is this possible in SQL?
I tried following but didn't work.
SQL> var val varchar2(20);
SQL> exec :val := 'test/one 7/8 test/2';
SQL> select :val,
substr(:val,(instr(:val,'/')+1),1) Prec,
substr(:val,(instr(:val,'/')-1),1) Prev,
ascii(substr(:val,(instr(:val,'/')+1),1)) PrecAscii,
ascii(substr(:val,(instr(:val,'/')-1),1)) PrevAsci,
(case
when ascii(substr(:val,(instr(:val,'/')+1),1)) between 48 and 57
then 'bingo'
when ascii(substr(:val,(instr(:val,'/')-1),1)) between 48 and
57 then 'bingo'
else
replace(:val,'/',',')
end ) derivedValue
from dual;
:VAL P P PRECASCII PREVASCI DERIVEDVALUE
------------------------------ - - ---------- ---------- --------------------------------
test/one 7/8 test/2 o t 111 116 test,one 7,8 test,2
I know it can be done in PL/SQL but is this correct approach?
Thanks for your help
October 20, 2003 - 9:45 pm UTC
it'll be hard in 9i and before (wait'll regexp in 10g -- it'll be a snap).
you'll need to write a plsql function to do it. I don't see a way to do it straight forward just using SQL and replace/substr/instr
INSTR
mo, March 04, 2004 - 7:06 pm UTC
Tom:
Two questions on 8i database:
1. If I want to strip a string from words ending with semicolon, how wo
uld you do that:
E.G:
If col1='Damaged; Missing; Other; Sky is blue'
I want to display:
'Sky is blue'.
2. I want to update a word in a string:
e.g.:
col1='This packet is for you'
I want to change the word packet to box so it will be:
col1='This box is for you'
Do I have to update the whole string or I can do search and replace.
March 04, 2004 - 7:30 pm UTC
1) i suppose 'Hello this; is; in; the; middle; World' should be 'Hello World'?
if so, it is is more complex than "show me the text after the last ; in the string", we'll need to parse it to accomplish this in 9i (or load a regular expression package into the db).
in 10g, regexp can do this:
ops$tkyte@ORA10G> select x,
trim( regexp_replace( ' ' || x, ' [A-Za-z0-9]*;', '' ) ) y from t;
X Y
-------------------------------------------------- --------------------
Damaged; Missing; Other; Sky is blue Sky is blue
Hello this; is; in; the; middle; World Hello World
2) replace. trim( replace ( ' ' || x || ' ', ' packet ', ' box ' ) )
INSR
mo, March 04, 2004 - 10:25 pm UTC
Tom:
For 1 above, and using 8i database can't I use owa_util regular expression procedures or a combination of INSTR and REPLACE to accomplish it?
March 05, 2004 - 7:52 am UTC
ops$tkyte@ORA9IR2> create or replace function f( p_str in varchar2, p_src in varchar2, p_rep varchar2 ) return varchar2
2 is
3 l_temp long := p_str;
4 begin
5 owa_pattern.change( l_temp, p_src, p_rep, 'g' );
6 return l_temp;
7 end;
8 /
Function created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select x, trim( f( ' ' ||x, ' [A-Za-z0-9]*;', '' ) ) y from t;
X Y
---------------------------------------- --------------------
Damaged; Missing; Other; Sky is blue Sky is blue
Hello this; is; in; the; middle; World Hello World
OWA_PATTERN
mo, March 05, 2004 - 3:47 pm UTC
Tom:
Thanks a lot, It works very nicely.
If I have one sentence with two words where the first one is "Missing Reels;" would I be able to do it in one SQL statement (take out the words with ";" and the word "Missing").
What I did is I ran the "F" function twice. I took the resulting string and ran it through the function again by looking for the word "Missing".
Thank you,
March 05, 2004 - 4:00 pm UTC
why would the word "missing" go way?
you asked to remove words ending with ';'
missing does not?
kumar
kumar, March 15, 2004 - 9:05 am UTC
great ..works perfectly.. this is exactly what we are looking for.
sort
mo, April 15, 2004 - 6:37 pm UTC
Tom:
I want to sort an alphanumberic field by the number inside of it.
For example, if it has
DE050A
DE1A
I want to write the "DE1A" and then "DE050A". 1 comes before 50 and so on.
Is there a way to strip the field from the alpha letters, and then sort the contents as numeric.
thank you,
April 15, 2004 - 6:40 pm UTC
to_number(translate(string,'ABC.....'),' ' ))
replace all occurrences
A reader, July 12, 2006 - 3:43 pm UTC
Hi
I have a text like this
SELECT f from A, B where a.id = :b1 a.text like '%:b2%' and a.id = b.id
I was wondering if I want to replace all :b? occurrences to something else such as :v? how can I do it? In Oracle 9i I dont see any function?
July 12, 2006 - 4:12 pm UTC
you are really looking for
a.text like '%:b2%'
or maybe:
a.text like '%' || :b2 || '%'
replace( string, ':b', ':v' )
will replace all occurences of the string :b with :v in the string "string"
oops I make a pardon
A reader, July 12, 2006 - 4:18 pm UTC
sorry, there were a mistake
the binds are
:1
:2
not :b1 or :b2!
Would like to change :1 and :2 ( I have up to :27! ) to :b1 and :b2. Sorry for the confusion.
July 12, 2006 - 5:26 pm UTC
version?
and nothing like "and x = ':1hello'" in there right?
9.2.0.6
A reader, July 12, 2006 - 5:30 pm UTC
Hi
I am using 9i so I cant use the regexp functions in 10g....
there wont be :1hello but there would be :1) for example.... do you think it's a good idea to isolate all stuffs with ":" so I always get
:1 space
:2 space
and so on?
July 12, 2006 - 5:51 pm UTC
but there need NOT be a space in general.
select * from t where x like '%'||:1||'%'
now what :)
we will probably have to write code for this.
Works well.
Ed, November 02, 2014 - 8:18 pm UTC
Worked well for me!!