-
need a piece of pl/sql to do this.....
a varchar2(200) field gets passed in with a bunch of text in it. I need to scan the text
for instances
of a "special char" if one or more is found it needs to replace the following text up
until the next blank space with ++++ as an example. my rough flow of it is below. does
any one know of anything existing like this?
grab text field
look for(1st, 2nd, 3rd, ...) speacial char
if
find 1st special char(example: @ )
and NOT eof (end of varchar2 200)
replace string following it until next blank space(starting at the char
after special char and go to first space and replace with newval)
special char++
else exit
return changed varchar2(200)
thanks in advance
SM
-
Well, all you need are the following functions.
InStr() to find the first occurance of the special character, and the first occurance of space following the first special character
SubStr() to snip out sections of the string
RPad() to pad out a string to a given length using a particular character
-
The following example replaces abbreviations with un-abbreviated words:
Code:
drop table full_job_ttl
/
create table full_job_ttl
as
select * from job_ttl
/
alter table full_job_ttl
add (full_title varchar2(200))
/
set serverout on size 1000000;
declare
type txt_typ is table of varchar2(200)
index by binary_integer;
type rowid_typ is table of rowid
index by binary_integer;
v_abb txt_typ;
v_una txt_typ;
v_ttl txt_typ;
v_full_ttl txt_typ;
v_row rowid_typ;
i pls_integer;
j pls_integer;
k pls_integer;
l pls_integer;
begin
select abbrev, un_abbrev bulk collect
into v_abb, v_una
from job_ttl_abbrev
;
select rowid, title bulk collect
into v_row, v_ttl
from full_job_ttl
;
for j in 1..v_abb.count
loop
if NVL(v_una(j),'?') = '?' then
v_una(j):='?'||v_abb(j);
end if;
end loop;
for i in 1..v_row.count
loop
v_full_ttl(i):=RTRIM(UPPER(v_ttl(i)));
for j in 1..v_abb.count
loop
if length(v_full_ttl(i)) > length(v_abb(j)) and
substr(v_full_ttl(i),1,length(v_abb(j))+1) = v_abb(j)||' ' then
v_full_ttl(i):=v_una(j)||substr(v_full_ttl(i),length(v_abb(j))+1);
end if;
k:=instr(upper(v_full_ttl(i)),' '||v_abb(j)||' ');
if k>0 then
v_full_ttl(i):=substr(v_full_ttl(i),1,k)||v_una(j)
||substr(v_full_ttl(i),k+length(v_abb(j))+1);
end if;
k:= length(v_full_ttl(i)) - length(v_abb(j));
if length(v_full_ttl(i)) > length(v_abb(j)) and
substr(v_full_ttl(i),k) = ' '||v_abb(j) then
v_full_ttl(i):=substr(v_full_ttl(i),1,k)||v_una(j);
end if;
end loop;
v_full_ttl(i):=RTRIM(INITCAP(v_full_ttl(i)));
dbms_output.put_line(v_ttl(i)||' -> '||v_full_ttl(i));
end loop;
forall i in 1..v_row.count
update full_job_ttl
set full_title = v_full_ttl(i)
where rowid = v_row(i)
;
commit;
end;
/
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
thanks guys
wow, its code like that that makes me happy im not a programer.....but unhappy I have to figure it out for this piece :(
this is going to be a challenge...
--SM
-
*Deleted*
sorry, double post
--SM
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|