General discussion

Locked

Oracle

By wbenson ·
I have a field in my database that is 100 characters long. I want to change characters 50, 51, 52, 53, 54 as follows:

currently: 2345U 7834U
want: 02345 07834

Essentially I'm trying to get rid of the end "U" and put a zero on thefront of the number. I need to do this generically as I have about 400 records that require this change. In other words, each of the 400 occurences could use different numbers (though they all end in U and I want to drop the U and put the zero in front).

This conversation is currently closed to new comments.

19 total posts (Page 2 of 2)   Prev   01 | 02
Thread display: Collapse - | Expand +

All Comments

Collapse -

Oracle

by srini In reply to Oracle

If your number sequences are of the same length (5 + space + 5), then try the following query...

SELECT '0' || SUBSTR(col1, 1, 4) || ' ' || '0' || SUBSTR(col1, 7, 4) FROM table;

Collapse -

Oracle

by wbenson In reply to Oracle

The question was auto-closed by TechRepublic

Collapse -

Oracle

by pratap_singh In reply to Oracle

Use Update statement as follows
for string starting at loc1 and len1 and

Update table1
set col1=
substr(col1,1,locstart)||'0'||substr(col1,loc1,len1)||
substr(col1,loc1+len1+1)
;

Collapse -

Oracle

by wbenson In reply to Oracle

The question was auto-closed by TechRepublic

Collapse -

Oracle

by vs In reply to Oracle

Update YTAB
set YF = substr(YF,1,49)||'0'||substr(YF,50,4)||substr(YF,55,46)
where substr(YF,54,1)='U';
-- that is the simplest and effective solution.

Collapse -

Oracle

by wbenson In reply to Oracle

The question was auto-closed by TechRepublic

Collapse -

Oracle

by srishankar.r In reply to Oracle

You Can try out the following update statement. But before proceeding try the
select version of the same update statement
or after updation view the record and then commit.

Let emp be the Table

EMP

EMP_REG_NO VARCHAR2(100)

update emp set
emp_reg_no = substr(emp_reg_no,1,49)
|| '0'
|| substr(emp_reg_no,51,4) ||
substremp_reg_no,56,(length(emp_reg_no) - 56))

Collapse -

Oracle

by wbenson In reply to Oracle

The question was auto-closed by TechRepublic

Collapse -

Oracle

by wbenson In reply to Oracle

This question was auto closed due to inactivity

Back to Web Development Forum
19 total posts (Page 2 of 2)   Prev   01 | 02

Related Discussions

Related Forums