CODEWITHSUNDEEP
stringoracle-database
sudhir kumar
sudhir kumar

Reputation: 115

Oracle varchar string replacement

I have a table (x) in a database and column(y) of data type varchar. This column includes values like 1. , 2\, .5, 2], 4.3 etc.

I am trying to replace all the values in column which have special characters except the ones that have a value after dot.

eg: I don't want to replace values like 2.3, 4.5, 7.1.

If there is no value after dot or a value starting with dot I would like to replace dot with null.

eg:

I tried below script but it didn't work

update <table_name> 
set <column_name> = regexp_replace(regexp_replace(column_name, '[^A-Z0-9 ]', '.'),
                                              ' {2,}', ' ')
/

Please help.

I am using Oracle 11g DB.

Upvotes: 0

Views: 76

Answers (1)

user5683823
user5683823

Reputation:

Here is one way. First we use a regular expression function to remove all characters OTHER THAN digits and period. Then we apply TRIM to the result, to trim (remove) the period only if it is the first or the last character.

The WITH clause is not part of the query; it is there only to create the test data for myself. Just use the query (from SELECT...) with your actual table and column names.

with
  inputs ( str ) as (
    select '2.'   from dual union all
    select '-4.2' from dual union all
    select '3?*'  from dual union all
    select '32.'  from dual union all
    select '.3'   from dual union all
    select '5$8'  from dual
  )
select str, trim( '.' from regexp_replace(str, '[^0-9.]') ) as new_str
from   inputs
;

STR  NEW_STR
---- -------
2.   2   
-4.2 4.2 
3?*  3   
32.  32  
.3   3   
5$8  58

Upvotes: 2

Related Questions