Help Needed, New to PLSQL [message #419013] |
Wed, 19 August 2009 10:33  |
learningplsql
Messages: 7 Registered: August 2009
|
Junior Member |
|
|
Hi!
I need to write a procedure for assignment.
This is just an example of what i need;
I Love PLSQL (ilp)
I need to write a procedure to convert ilp inside the braces into uppercase.
This is how the final output must look:
I Love PLSQL (ILP)
Thanks in advance,
K
|
|
|
|
|
|
|
Re: Help Needed, New to PLSQL [message #419025 is a reply to message #419023] |
Wed, 19 August 2009 11:06   |
learningplsql
Messages: 7 Registered: August 2009
|
Junior Member |
|
|
since am new to plsql am still not very good with the syntax, but i can tell u wat i had in mind.
-Search for '(' and ')' using instr.
-substr and get the text in between and convert to uppercase.
-Concatenate and join the first and second half.
|
|
|
|
Re: Help Needed, New to PLSQL [message #419027 is a reply to message #419025] |
Wed, 19 August 2009 11:09   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
The best way to learn any language is by trying, reading the manual and making lots of errors.
You gain nothing by us telling how to do it.
Try to do what you thought to do, step by step. So far, it looks good.
|
|
|
|
|
|
|
Re: Help Needed, New to PLSQL [message #419048 is a reply to message #419030] |
Wed, 19 August 2009 11:49   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Have you actually tried anything at all?
All we want is some evidence that you have tried, and that you're not using us to do your homework for you.
You need INSTR and SUBSTR
SUBSTR is used to select a number of characters from a string starting at a given position.
INSTR is used to determine the position of a character within a string.
So, you need to use INSTR to determine the position of the '(' and ')' characters.
You then use SUBSTR to split the string into 3 pieces - Everything upto and including the '(', the characters between the '(' and the ')', and the characters after and including the ')'
Once you've got this, it's a simple job to convert the string as required.
|
|
|
Re: Help Needed, New to PLSQL [message #419050 is a reply to message #419048] |
Wed, 19 August 2009 12:11   |
learningplsql
Messages: 7 Registered: August 2009
|
Junior Member |
|
|
i am trying 
Thats what i did till now:
select upper(substr('I Love PLSQL(ipl)', instr('I Love PLSQL(ipl)','(',1,1),
instr('I Love PLSQL(ipl)',')',1,1)
))from dual;
this gives; (IPL)
|
|
|
Re: Help Needed, New to PLSQL [message #419052 is a reply to message #419050] |
Wed, 19 August 2009 12:16   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Good, you have the center part, now get the first and last parts, and in the end concatenate them.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Regards
Michel
[Updated on: Wed, 19 August 2009 12:17] Report message to a moderator
|
|
|
Re: Help Needed, New to PLSQL [message #419055 is a reply to message #419052] |
Wed, 19 August 2009 12:48  |
learningplsql
Messages: 7 Registered: August 2009
|
Junior Member |
|
|
I Think i finally cracked it sort of!!
select substr('I Love PLSQL (ilp)', instr('I Love PLSQL (ilp)','G',1,1),
instr('I Love PLSQL (ilp)',' ',1,3)-2
)
|| ' ' ||
upper(substr('I Love PLSQL (ilp)', instr('I Love PLSQL (ilp)','(',1,1),
instr('I Love PLSQL (ilp)',')',1,1)
))from dual;
Output:
I Love PLSQ (ILP)
Now i hav to write logic for this, thanks a lot guy! I think i can take it forward from here on!! 
Thanks Again!
[Updated on: Wed, 19 August 2009 12:54] Report message to a moderator
|
|
|