[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

ORACLE -- SUBSTR update ?

Posted on 2010-11-08
4
Medium Priority
?
1,140 Views
Last Modified: 2013-12-18
I HAVE AN ORG_CODE varchar2 column,
RUN the BELOW STATEMENT WHICH SHOULD
update all 100+ of MY ORG_CODE = '130xxx'
RECORDS to ORG_CODE = '100xxx', BUT
updates them to '231' instead.

How can I fix ?

update COMPANY_POSITION_TAB
--set ORG_CODE = '100101' -- works
set ORG_CODE = '100' + SUBSTR(ORG_CODE,4,3) -- fails
where SUBSTR(ORG_CODE,1,3) = '130'
0
Comment
Question by:finance_teacher
  • 2
4 Comments
 
LVL 9

Expert Comment

by:jerrypd
ID: 34084008
i would verify the whole ORG_CODE before going any further.
The code looks ok to me, but not knowing what the 4th 5th and 6th chars are makes it a bit tough to diagnose...
0
 
LVL 10

Accepted Solution

by:
laneduncan earned 2000 total points
ID: 34084029
Oracle doesn't use the '+' character for concat.  You'd need to do something like this, instead:

set ORG_CODE = '100' || SUBSTR(ORG_CODE,4,3)
where SUBSTR(ORG_CODE,1,3) = '130'
0
 
LVL 9

Expert Comment

by:jerrypd
ID: 34084051
oops! I missed that '+' sign - - good catch!!
0
 
LVL 32

Expert Comment

by:awking00
ID: 34105424
Another way -
UPDATE COMPANY_POSITION_TAB
SET ORG_CODE = REGEXP_REPLACE(ORG_CODE,'^130','100');
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question