Home » SQL & PL/SQL » SQL & PL/SQL » Help Needed, New to PLSQL
Help Needed, New to PLSQL [message #419013] Wed, 19 August 2009 10:33 Go to next message
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 #419015 is a reply to message #419013] Wed, 19 August 2009 10:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Really, this ought to go in Homework.

Look at REGEXP_REPLACE
Re: Help Needed, New to PLSQL [message #419019 is a reply to message #419013] Wed, 19 August 2009 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you are in a version below 10g you have to use SUBSTR, INSTR, UPPER.

Regards
Michel
Re: Help Needed, New to PLSQL [message #419022 is a reply to message #419019] Wed, 19 August 2009 10:58 Go to previous messageGo to next message
learningplsql
Messages: 7
Registered: August 2009
Junior Member
yes am using a older ver 9i, can u show me how it can be done.. I have tried but in vain Sad . I jus cant get it to work.
thanks!
Re: Help Needed, New to PLSQL [message #419023 is a reply to message #419022] Wed, 19 August 2009 11:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ok, show what different ways you tried.
Re: Help Needed, New to PLSQL [message #419025 is a reply to message #419023] Wed, 19 August 2009 11:06 Go to previous messageGo to next message
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 #419026 is a reply to message #419025] Wed, 19 August 2009 11:08 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
learningplsql wrote on Wed, 19 August 2009 09:06
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.



VERY GOOD, now just code it up!
Re: Help Needed, New to PLSQL [message #419027 is a reply to message #419025] Wed, 19 August 2009 11:09 Go to previous messageGo to next message
Frank
Messages: 7880
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 #419030 is a reply to message #419027] Wed, 19 August 2009 11:13 Go to previous messageGo to next message
learningplsql
Messages: 7
Registered: August 2009
Junior Member
can u guys guide me to some samples, which i can look at.
Re: Help Needed, New to PLSQL [message #419032 is a reply to message #419030] Wed, 19 August 2009 11:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
tahiti.oracle.com is the home of all the documentation you'll ever need for Oracle.
Google is a good entry point when searching for examples
Re: Help Needed, New to PLSQL [message #419033 is a reply to message #419032] Wed, 19 August 2009 11:18 Go to previous messageGo to next message
learningplsql
Messages: 7
Registered: August 2009
Junior Member
okie , thanks for the tips, will give it a try.
Re: Help Needed, New to PLSQL [message #419047 is a reply to message #419033] Wed, 19 August 2009 11:49 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
learningplsql wrote on Wed, 19 August 2009 09:13
can u guys guide me to some samples, which i can look at.


http://asktom.oracle.com
Re: Help Needed, New to PLSQL [message #419048 is a reply to message #419030] Wed, 19 August 2009 11:49 Go to previous messageGo to next message
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 Go to previous messageGo to next message
learningplsql
Messages: 7
Registered: August 2009
Junior Member
i am trying Sad

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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous message
learningplsql
Messages: 7
Registered: August 2009
Junior Member
I Think i finally cracked it Smile 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!! Smile

Thanks Again!

[Updated on: Wed, 19 August 2009 12:54]

Report message to a moderator

Previous Topic: how to get the procedure name inside the package body
Next Topic: Strange behavior using function in SQL and PL/SQL
Goto Forum:
  


Current Time: Sun Dec 04 10:23:55 CST 2016

Total time taken to generate the page: 0.09394 seconds