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
AnneYourPointIsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rbrookerCommented:
Hi,

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;

remove words ALTER and UPDATE from a string :
SELECT lower(REPLACE(REPLACE(upper('alter and replace'), 'REPLACE', ''), 'ALTER', '')) my_new_string
FROM dual;

convert a string to upper case / lower case :
SELECT upper('abcd') uppercase_string,
       lower('ABCD') lowercase_string
FROM dual;

convert abcd to $9999:
not sure what you mean with this one...  you cannot convert the string 'abcd' to a number.

good luck.
:)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sathyagiriCommented:
Find the position of the 'a, and, the" in the sring

select instr(colname,''',1) from dual;

select instr(colname,'"',1) from dual;

Remove the words ALTER and UPDATE from a string.

select replace(replace('string','alter ,''),'update','') from dual;

convert the string to upper case and lower case
select upper(string) from dual;

select lower(string) from dual;

convert abcd to $9999.
select  translate('abcd','abcd','$9999') from dual;
 or
select replace('abcd',$9999') from dual;

AnneYourPointIsAuthor Commented:
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
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

SujithData ArchitectCommented:
"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.   "

What you are changing here is the starting position of the search within the string. If you want to find the second occurance of 'a' you have to use

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

For third occurance

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

and so on

convert abcd to $9999 :

There are n number of ways for doing this.
The simplest one would be

select replace('My abcd','abcd','$9999') from dual;

Or you can use

select replace(translate('My abcd','abcd','9999'),'9999','$9999') from dual

sapnamCommented:
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
MohanKNairCommented:
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;
/
MohanKNairCommented:
select to_char(1210.73, '$9,999.00') from dual;

Oracle Number formats
http://www.ss64.com/orasyntax/numfmt.html
cjardCommented:
heavens above. this needs serious cleanup..

Lisa, you need to ask the questions more clearly. Everyone here is guessing at what this means:
Find the position of the 'a, and, the" in the sring
------------------------------------------------------

Some people have assumed youre searching for the letter A in the given string of: 'a, and, the"
I think you need to tidy up the question a little..

here is how you use INSTR:
http://www.techonthenet.com/oracle/functions/instr.php

If you do not feel you could find the position in a string, of a particular substring after reading that article, then feel free to ask for more help.



Remove the words ALTER and UPDATE from a string
-------------------------------------------------------------
What do you mean?..
Do you want to remove this from a string: ALTER and UPDATE
Do you want to remove this from a string: ALTER
And also you want to remove this from a string: UPDATE

see here:
http://www.techonthenet.com/oracle/functions/replace.php

Again if after reading that youre still confused, please return and ask a clear question




convert the string to upper case and lower case
------------------------------------------------------
http://www.techonthenet.com/oracle/functions/upper.php
http://www.techonthenet.com/oracle/functions/lower.php




convert abcd to $9999
-------------------------
This makes little sense too.
Does this signify a number: abcd
Does it require changing so that a becomes 9, b becomes 9, c becomes 9, d becomes 9?

http://www.techonthenet.com/oracle/functions/translate.php

Here is how to convert a string to a number:
http://www.techonthenet.com/oracle/functions/to_number.php

Here is how to format a number so it has a dollar sign on:
http://www.techonthenet.com/oracle/functions/to_char.php


Dont forget, that due to the ambuguity of your question, the following SQL answers the question:

'$' || REPLACE('Auction guide price abcd', 'abcd', '9999')



You do not use TRANSLATE because translate takes a list of characters and turns them into other characters. It does not turn a substring into another substring. See the help pages i posted on REPLACE and TRANSLATE. It is important to know the difference:

REPLACE(myStr, 'abc', '123')
----------------------------------
every occurrence of abc is replaced by 123. characters are not affected individually


TRANSLATE(myStr, 'abc', '123')
----------------------------------
every occurrence of a is replaced by 1
every occurrence of b is replaced by 2
every occurrence of c is replaced by 3
characters are affected individually



Hope this helps, TOTN is a great site for oracle, and we have the ability to answer anything it doesnt, but do *please* ask a clear question :)



cjardCommented:
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
AnneYourPointIsAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.