Link to home
Create AccountLog in
Avatar of AnneYourPointIs
AnneYourPointIsFlag for United States of America

asked on

Replace function

Hello experts,

This is probably a very easy question but i'm new to Oracle and I'm working through some problems in a book I picked up which prepares you for the certification exam.  Here's the question.  

Using the built in functions

Find the position of the 'a, and, the" in the sring
Remove the words ALTER and UPDATE from a string.  
convert the string to upper case and lower case.  (I've played with this and think I can handle it unless you think there's something I need to know.)
convert abcd to $9999.

Any help would be greatly appreciated.  

TIA,

Lisa
ASKER CERTIFIED SOLUTION
Avatar of rbrooker
rbrooker
Flag of New Zealand image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of AnneYourPointIs

ASKER

find the position of a letter in a string :
SELECT instr(my_string, 'a', 1) -- finds first occurance
FROM dual;
SELECT instr(my_string, 'a', 2) -- finds second occurance
FROM dual;

gives me the number 21 no matter how many times I advance the number by 1.
The first 'a' does occur at 21 but there are more.  This is what I used, if you can help with this it would be greatly appreciated.

find the position of 'a' 'the' in the string
SELECT instr('both the UPDATE commands and the ALTER TABLE command','a',1)
FROM dual;

returns 21, if you change 1 to 2 it still returns 21.  


also...

I used Sathy's solution for the conversion of abcd to $9999.  This is how I ended up accomplishing this:

select (concat(translate('My abcd','abcd',$9999),'9')
from dual;

I added concat because without it, the returned number was $999.

Lisa
SOLUTION
Avatar of Sujith
Sujith
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of sapnam
sapnam

You have to give

SELECT instr('both the UPDATE commands and the ALTER TABLE command','a',1,1)
FROM dual;

to get the 1st occurence from position 1 of your string

SELECT instr('both the UPDATE commands and the ALTER TABLE command','a',1,2)
FROM dual;

to get the 2nd  occurence from position 1 of your string

so after 'a' you have to give 2 digits, the first is the position from where you want to start searching and the second is the number of occurence you want to search
declare
v_str varchar2(1000) := 'both the UPDATE commands and the ALTER TABLE command';
i1 number :=1;
n1 number;
BEGIN
LOOP
n1 := instr(v_str, 'a', i1);
exit when nvl(n1,0)=0;
dbms_output.put_line(n1);
i1:=i1+1;
END LOOP;
END;
/
select to_char(1210.73, '$9,999.00') from dual;

Oracle Number formats
http://www.ss64.com/orasyntax/numfmt.html
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Correction to my advice:



Dont forget, that due to the ambuguity of your question, the following SQL answers the question:
REPLACE('Auction guide price abcd', 'abcd', '$9999')


i.e.
              FIND abcd
REPLACEWITH $9999
cjard ,

I'm sorry if you feel like the questions are ambiguous and I do appreciate your help.  However, you have what I have.  The questions are taken right out of the book.  I'm working through each problem and I've been successful with most of them.  The questions that I posted were the one's I was racking my brain to figure out.  It' s just like a question I'm working on now.  change the sysdate to July 20th, 2004.  In the chapter it discusses changing the sysdate to a long date and a short date and military time, etc.  The question at the end of the chapter is change the sysdate to July 20th, 2004.  It's the questions in the book not me that is ambiguous.  Maybe its good that they are ambiguous because I've gotten a lot of excellent posts.  You know the old saying, "there's more than one way to skin a cat."  The posts are certainly broadening my thought process and that there is not just one way of working through these problems.

Lisa