CODEWITHSUNDEEP
oracle-database
Shulaz Shan
Shulaz Shan

Reputation: 175

Replace string in oracle

I have a number column, I need to replace the first number by 7 in oracle. How to replace guys?

number    want_number
4789654   7789654
2754678   7754678
1765689   7765689

Upvotes: 0

Views: 65

Answers (3)

Alex Poole
Alex Poole

Reputation: 191570

You could manipulate as numbers, rather than converting to (and presumably later back from) strings:

with your_table (original) as (
            select 4789654 from dual
  union all select 2754678 from dual
  union all select 1765689 from dual
  union all select 999 from dual
  union all select 1000 from dual
  union all select 1001 from dual
)
select original,
  original
    - trunc(original, -floor(log(10, original)))
    + 7 * power(10, floor(log(10, original))) as wanted
from your_table;

  ORIGINAL     WANTED
---------- ----------
   4789654    7789654
   2754678    7754678
   1765689    7765689
       999        799
      1000       7000
      1001       7001

The floor(log(10, original) gives you the magnitude of the number. As an example, for your first original value 4789654 that evaluates to 6. If you then do trunc(original, -floor(log(10, original))) that is trunc(4789654, -6), which zeros the six least significant digits, giving you 4000000. Subtracting that from the original value gives you 789654. Then power(10, floor(log(10, original))) gives you power(10, 6) which is 1000000, multiplying that by 7 gives you 7000000, and adding that back on gives you 7789654.

(This won't work if your original value is <= zero, but that looks unlikely?)

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143103

Or, a substring option with concatenation:

SQL> with test (num) as
  2    (select 4789654 from dual union all
  3     select 2754678 from dual union all
  4     select 1765689 from dual
  5    )
  6  select num, '7' || substr(num, 2) wanted_num
  7  from test;

       NUM WANTED_NUM
---------- --------------------
   4789654 7789654
   2754678 7754678
   1765689 7765689

SQL>

Upvotes: 1

Hellmar Becker
Hellmar Becker

Reputation: 2982

For instance

REGEXP_REPLACE(number, '^\d', '7')

should work.

Upvotes: 2

Related Questions