CODEWITHSUNDEEP
sqlregexoracle-databaseformatting
PythonDeveloper
PythonDeveloper

Reputation: 404

Remove special characters except Hyphen and Space in Oracle String Data

I need to remove all the special characters except Hyphen and Space in Oracle String Data using Reg_replace function.Please help. For ex:

Input-> My Name is #name1 Output -> My Name is name1

Input-> this is my add-ress#" Output-> this is my add-ress

Input-> can we remov-e this'; Output->can we remov-e this

Upvotes: 0

Views: 13301

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

You can use the following 

with t(str) as
(
 select 'this is my add-ress#&%! : 198' from dual
)
select regexp_replace(str,'[^-0-9A-Za-z ]','') as "Result String"
  from t;

Result String
-----------------------
this is my add-ress  198

Upvotes: 0

MT0
MT0

Reputation: 168720

You can use [^[:alnum:] -] as a regular expression to match the values you want to replace.

Oracle Setup:

CREATE TABLE test_data( value ) AS
  SELECT 'this is my add-ress#\' FROM DUAL UNION ALL
  SELECT 'My Name is #name1' FROM DUAL UNION ALL
  SELECT 'can we remov-e this' FROM DUAL;

Query:

SELECT value,
       REGEXP_REPLACE( value, '[^[:alnum:] -]', NULL ) AS replaced_value
FROM   test_data

Output:

VALUE                 | REPLACED_VALUE     
:-------------------- | :------------------
this is my add-ress#\ | this is my add-ress
My Name is #name1     | My Name is name1   
can we remov-e this   | can we remov-e this

db<>fiddle here

Upvotes: 1

Related Questions