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

Tags:
Data/Application Integration
Database
IBM DB2
Oracle
Oracle 9i
Oracle administration
Oracle Business Applications
SQL
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Try the following:

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

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Randym
    Oracle has the Replace function but that will replace all occurrences 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');
    1,740 pointsBadges:
    report
  • PasztorZ
    A variation to the theme: UPDATE table SET field = substr(FIELD,1,3)|| '08' || substr(FIELD,6) WHERE substr(FIELD,4,2) = '18';
    0 pointsBadges:
    report
  • IdongesitDaniel
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/sql-update-command-to-update-certain-digits-in-a... (0) Comments Read [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following