CODEWITHSUNDEEP
sqloracle-database
Jared
Jared

Reputation: 2954

Replace special character apostrophe with normal apostrophe

I have a field which is getting data that contains a special character type of apostrophe outside of the normal oracle ascii range of 0-127. I am trying to do a replace function on this but it keeps being switched to a ? in the DDL. Looking for another way to do the replace

This works in a query but switches when put in the DDL for a view

regexp_replace(field_name,'’',chr(39))

switches to

regexp_replace(field_name,'?',chr(39))

A dump function shows that oracle is storing the apostrophe as three characters of ascii 226,128,153. I tried to write the replace on a concatenation of those but that didn't work either.

Upvotes: 1

Views: 2985

Answers (1)

Martin Schapendonk
Martin Schapendonk

Reputation: 13506

First, examine the original data that contains the weird apostrophe. I'm not convinced that it is indeed three characters. Use this:

select value
, substr(value, 5, 1) one_character
, ascii(substr(value, 5, 1)) ascii_value
from table;

This would isolate the 5th character from a column value and its ascii value. Adjust the 5 to the place where the weird apostrophe is located.

When you have the ascii value, use plain replace like this to get rid of it (regexp_replace seems overkill):

replace(value, chr(ascii_value_of_weird_apostrophe), chr(39));

Upvotes: 2

Related Questions