Avatar of AnneYourPointIs
AnneYourPointIs
Flag 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
Oracle Database

Avatar of undefined
Last Comment
AnneYourPointIs

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
rbrooker

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
sathyagiri

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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
MohanKNair

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;
/
Your help has saved me hundreds of hours of internet surfing.
fblack61
MohanKNair

select to_char(1210.73, '$9,999.00') from dual;

Oracle Number formats
http://www.ss64.com/orasyntax/numfmt.html
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
cjard

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
AnneYourPointIs

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.