Home > IT Answers > Oracle > sql update command to update certain digits i...
Help

Question

  Asked: Oct 6 2006   11:26 AM GMT
  Asked by: IdongesitDaniel

sql update command to update certain digits in a row of a column with VARCHAR2(9) DATATYPE.


Oracle, Database, DB2, SQL, Oracle administration, Oracle Business Applications, Data/Application Integration, Oracle 9i

Please I need an sql command that I can use to update only the fourth and fifth digits in a particular column in an Oracle 9i database.The column has a VARCHAR2(9) datatype.My oracle database is on windows 2003 environment.

Eg: The row in question reads '063150987' and I want to replace the fourth and fifth digits('15') with '08' for all the rows in that column.

Thank you

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window
Help

Answer Wiki (Improve, edit or add to this answer)

IMPROVE THIS ANSWER
View History
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
Last Answered: Oct 6 2006  12:07 AM GMT  by kurthp




Try the following:

UPDATE table SET field = substr(FIELD,1,3)|| replace(substr(FIELD,4,2),'15','08')|| substr(FIELD,6,4);
  • AddThis Social Bookmark Button

Browse more Questions and Answers on Oracle, Database and Development.

Looking for relevant Oracle Whitepapers? Visit the SearchOracle.com Research Library.


RSS - Discussions Help

Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register

randym  |   Oct 6 2006  12:20PM GMT

Oracle has the Replace function but that will replace all occurances of one value to another. So if you can have multiple “15″ in the field, replace can’t be used for your situation. For your example of replacing 15 with 08, try something like this:

update table
set field = substr(field,1,3) || ‘08′ || substr(field,6,4))
where substr(field,4,2)=’15′;

If the rows will only have a 15 in position 4 and 5, you could use:

update table
set field = replace(field,’15′,’08′);

 

PasztorZ  |   Oct 7 2006  3:53AM GMT

A variation to the theme:

UPDATE table SET field = substr(FIELD,1,3)|| ‘08′ || substr(FIELD,6)
WHERE substr(FIELD,4,2) = ‘18′;