ORACLE -- SUBSTR update ?

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'
finance_teacherAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
laneduncanConnect With a Mentor Commented:
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
 
jerrypdCommented:
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
 
jerrypdCommented:
oops! I missed that '+' sign - - good catch!!
0
 
awking00Commented:
Another way -
UPDATE COMPANY_POSITION_TAB
SET ORG_CODE = REGEXP_REPLACE(ORG_CODE,'^130','100');
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.