How to replace Special Characters in Oracle

How to replace Special Characters in Oracle

Use regexp_replace (column_name,'[]~!@#$%^&*|?]', NULL ) to replace Special character .

Example:

CREATE TABLE test_chr

(

   name           VARCHAR (10)

 , company_name   VARCHAR (10)1         `

);

INSERT INTO test_chr

     VALUES ('Robert', 'Oracle@#');

INSERT INTO test_chr

     VALUES ('Mallinga', 'Telstra%^');

Select * from test_chr;

NAME	COMPANY_NAME
Robert	Oracle@#
Mallinga	Telstra%^

Let’s remove the special Characters:

SELECT name

     , company_name orig_company_name

     , REGEXP_REPLACE (company_name, '[]~!@#$%^&*|?]', NULL) company_name

  FROM test_chr;

Final Output

NAME	ORIG_COMPANY_NAME	COMPANY_NAME
Robert	Oracle@#	Oracle
Mallinga	Telstra%^	Telstra

2)  Length of NULL field is NOT NULL

Today, I have encountered below Issue, There is no Value in the field, But the length is not NULL.

Let’s  have a look  at the data :

NAME	ORIG_COMPANY_NAME	LENGTH(COMPANY_NAME)
Robert5		13
Mallinga5		12

Let’s  find the Characters in the table -> Use DUMP command to get the desired output .

DUMP -> this will tell you how to extract dump information including special characters from a string expression and a column.

It Returns VARCHAR2 value, which contains the  datatype code, length (in bytes), and internal representation of expr

SELECT name

     , company_name orig_company_name

     , dump(company_name)

  FROM test_chr;

NAME	ORIG_COMPANY_NAME	DUMP(COMPANY_NAME)
Robert5		Typ=1 Len=13: 0,0,0,0,0,0,0,0,0,0,0,0,0
Mallinga5		Typ=1 Len=12: 0,0,0,0,0,0,0,0,0,0,0,0

How to Remove Chr(0) From Column

SELECT name

     , length(replace(company_name, CHR(0))) len_company_name

     , replace(company_name, CHR(0)) company_name

  FROM test_chr;

Output :

NAME	LEN_COMPANY_NAME	COMPANY_NAME
Robert5	<Null>	<Null>
Mallinga5	<Null>	<Null>

I Hope this has been explained , Please leave a comment for further details .