Hi,

I have a column in table that have yet text in fields. Some of fields have in the end of text: distance: R1: a R2: b R3: c mm mm mm
I want to insert before the first mm d/a, the second mm d/b and the third mm d/c. It will be : distance R1: a R2: b R3: c d/a mm d/b mm d/c mm

Please can you help me to write the update table code as update table set column = ??.

So example: in items we have R1: 43.5 R2: 44 R3: 45 mm mm mm. There are about 17000 row containing this informations. I want to introduce before mm a calculation: 337.5/43.5 = 7.76 mm for the first one. In final it will be R1: 43.5 R2: 44 R3: 45 7.76 mm 7.67 mm 7.5 mm.

I think perhaps I must use regexp_replace in update table:
update table set column = regexp_replace (column, ....);

The regexp_replace is very hard to understand. That is my problem.

Thanks.

select 
  rtrim ( replace ( s, 'mm' ) ) || ' ' || 
  round ( 337.5 / 
    substr ( s, 
      instr ( s, 'R1: ') + 4,
      instr ( s, 'R2: ') - instr ( s, 'R1: ') - 4
    ), 2
  ) || ' mm ' ||
  round ( 337.5 / 
    substr ( s, 
      instr ( s, 'R2: ') + 4,
      instr ( s, 'R3: ') - instr ( s, 'R2: ') - 4
    ), 2
  ) || ' mm '
from (
  select 'R1: 43.5 R2: 44 R3: 45 mm mm mm' s from dual
)

RTRIM(REPLACE(S,'MM'))||''||ROUND(337.5
---------------------------------------
R1: 43.5 R2: 44 R3: 45 7.76 mm 7.67 mm