| Remove Second Occurrence of a word in String [message #642161] |
Fri, 04 September 2015 04:09  |
 |
malya
Messages: 22
Registered: August 2015
Location: Hyderabad, India
|
Junior Member |
|
|
This can be very stupid question but how to remove second occurrence of a word in a string.
In Oracle Form, I am creating a string & storing in display field based on the data block values. Message has lot of repetitive words.
Sample string is :
tmp_msg := SP10/XYZ(F NIL)SP10/XYZ(L NIL)SP1(F NIL)SP1(L NIL)SP1(S NIL)
In above string, want to remove second occurrence of SP10/11A and SP1. Tried lot of instr, replace, substr but unable to get it right.
Please help.
|
|
|
|
|
|
|
|
| Re: Remove Second Occurrence of a word in String [message #642164 is a reply to message #642163] |
Fri, 04 September 2015 04:31  |
 |
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
There are 3 occurrences of SP1 that string. Which one(s) do you want to remove? Just one? Two of them? What would the final result look like?
Are "SP10/XYZ" and "SP1" fixed and you'll want to remove just these strings, or are there different TMP_MSG values (such as XV1(1)XV1(2)) so you'd want to remove the second "XV1" which, actually, means that you'd first have to analyze TMP_MSG, find out which strings are duplicates, and then remove one of them? If that's so, what are the rules that make some string "a duplicate"? In your TMP_MSG, "S" itself is duplicated, or "10", or "NIL".
BTW, I'm moving your question into our (PL/)SQL forum as the problem isn't strictly Forms related.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Remove Second Occurrence of a word in String [message #642185 is a reply to message #642183] |
Fri, 04 September 2015 10:07 |
|
malya
Messages: 22
Registered: August 2015
Location: Hyderabad, India
|
Junior Member |
|
|
its Form Code on a button (when button press trigger)-
-------------
loop on data block;
Data Block Appear like this
ProjectCode Product Figures Remarks
----------- ------- ------- -------
SP10 LENOVO 853 NA
SP10 DELL 980 NOSP
SP10 INTEL 90 HBH
SP10/11A BOSS 180 ING
SP10/11A ORAFAQ 777 VHJ
Now I want to create message like below (Desired Output)
tmp_msg = SP10(LENOVO/853/NA, DELL/980/NOSP, INTEL/90/HBH)SP10/11A(BOSS/180/ING,ORAFAQ/777/VHJ)
Declare
tmp_msg VARCHAR2(1000);
LOOP
IF instr(tmp_msg,:project,1,1) <> 0 THEN
:project ='';
END IF;
tmp_msg := Project||'('||product||'/'||figures||'/'||remarks;
END Loop;
With above code, I am able to disappear second & third occurrence of SP10 but SP10/11A has SP10 so SP10/11A is not displaying for first time.
[mod-edit: code tags added by bb]
[Updated on: Fri, 04 September 2015 16:15] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Remove Second Occurrence of a word in String [message #642200 is a reply to message #642185] |
Fri, 04 September 2015 18:52 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You should be able to adapt the following to your form situation.
SCOTT@orcl> SELECT * FROM data_block
2 /
PROJECTCODE PRODUCT FIGURES REMARKS
----------- ------- ---------- -------
SP10 LENOVO 853 NA
SP10 DELL 980 NOSP
SP10 INTEL 90 HBH
SP10/11A BOSS 180 ING
SP10/11A ORAFAQ 777 VHJ
5 rows selected.
SCOTT@orcl> DECLARE
2 tmp_projectcode VARCHAR2(1000) := '';
3 tmp_msg VARCHAR2(1000) := '';
4 BEGIN
5 FOR r IN (SELECT * FROM data_block ORDER BY projectcode)
6 LOOP
7 IF tmp_projectcode IS NULL THEN
8 tmp_msg := tmp_msg || r.projectcode || '(';
9 tmp_projectcode := r.projectcode;
10 ELSIF r.projectcode != tmp_projectcode THEN
11 tmp_msg := RTRIM (tmp_msg, ', ') || ')' || r.projectcode || '(';
12 tmp_projectcode := r.projectcode;
13 END IF;
14 tmp_msg := tmp_msg || r.product || '/' || r.figures || '/' || r.remarks || ', ';
15 END LOOP;
16 tmp_msg := RTRIM (tmp_msg, ', ') || ')';
17 DBMS_OUTPUT.PUT_LINE (tmp_msg);
18 END;
19 /
SP10(LENOVO/853/NA, DELL/980/NOSP, INTEL/90/HBH)SP10/11A(BOSS/180/ING, ORAFAQ/777/VHJ)
PL/SQL procedure successfully completed.
|
|
|
|
|
|
| Re: Remove Second Occurrence of a word in String [message #642205 is a reply to message #642201] |
Sat, 05 September 2015 07:15 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
SQL> select *
2 from v$version
3 /
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> select xmlagg(
2 xmlelement(
3 e,
4 projectcode || '(' ||
5 rtrim(
6 xmlagg(
7 xmlelement(
8 e,
9 product || '/' || figures || '/' || remarks,
10 ', '
11 ).extract('//text()')
12 ),
13 ', '
14 ) || ')'
15 ).extract('//text()')
16 ) str
17 from data_block
18 group by projectcode
19 /
STR
------------------------------------------------------------------------------------------------------
SP10(LENOVO/853/NA, DELL/980/NOSP, INTEL/90/HBH)SP10/11A(BOSS/180/ING, ORAFAQ/777/VHJ)
SQL>
SY.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
