| insertion point [message #83180] |
Tue, 19 August 2003 08:15  |
zakariya
Messages: 4
Registered: August 2003
|
Junior Member |
|
|
Dear Shailender,
>
> Thanks for ur attempt to help me. I cannot use that in my problem.
> That was just an example in which case u know it is in the first space
> we have to insert the text. But I need to know where the insertion
> point is located which can be after n spaces or n characters away (say
> no space at all).
>
> Let me know if u require more explanation.
>
> Thanks and regards
>
> ZAKARIYA
>
|
|
|
|
| Re: insertion point [message #83188 is a reply to message #83180] |
Tue, 19 August 2003 22:21  |
Shailender Mehta
Messages: 49
Registered: June 2003
|
Member |
|
|
ZAKARIYA,
I din't quite understand your requirement. Insertion point is something which YOU SHOULD KNOW BEFOREHAND.
In the previous example, the insertion point was a space between the two words
(i.e. "My Text" which becomes "My Beautiful Text") in which case I used INSTR
built-in to concatenate and built the new string - Easy Stuff !!!.
I have a simple PL/SQL function which does the following :-
1) Insert a new word after 'n' spaces in a sentence
Or
2) Insert a new word after 'n' characters
In both cases, I stress once again that you should know EXACTLY the position
to insert the new text.
In the following example, I am tring to Insert text after the 3rd space " ",
so the output expected will be "This is My Beautiful Text".
i.e. To append the string after 'n' characters, call the script in the following
manner,
iPosn := fn_rtnPos (iString,Null,7);
so the output expected will be
"My Text" will then become "My TextBeautiful"
/****** Part of the code *****/
Set SerVerOutput On Size 999999;
Declare
iPosn Number;
iString Varchar2(100) := 'This is My Text';
iStringToAppend Varchar2(30) := 'Beautiful';
iNewStr Varchar2(100) := Null;
FUNCTION fn_rtnPos ( pInStr In varchar2
,pAfterNumSpaces In Number
,pAfterNumChars In Number
) Return Number Is
lChrFound Number := 0;
lChrFoundAtPosn Number := 0;
Begin
For i in 1..Length(pInStr) Loop
If ( pAfterNumSpaces Is Not Null ) Then
If ( Substr(pInsTr,i,1) = ' ' )
Then
lChrFoundAtPosn := i;
lChrFound := Nvl(lChrFound,0) + 1;
If ( Nvl(lChrFound,0) = pAfterNumSpaces ) Then
Return (i);
End If;
End If;
Else
lChrFound := Nvl(lChrFound,0) + 1;
lChrFoundAtPosn := i;
If ( Nvl(lChrFound,0) = pAfterNumChars ) Then
Return (i);
End If;
End If;
End Loop;
Return (lChrFoundAtPosn);
End fn_rtnPos;
/*----- Begin script ----- */
Begin
iPosn := 0;
iPosn := fn_rtnPos (iString,3,Null); /* 3 = Append after 3rd space */
/* 3rd parameter is Null because, looking for space */
If ( Nvl(iPosn,0) != 0 ) Then
iNewStr := Substr(iString, 1, iPosn) || iStringToAppend
|| ' ' || Substr(iString, iPosn+1, Length(iString));
End If;
Dbms_Output.Put_Line ('New Str = ' || iNewStr);
End;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
