I have this data “my shortterm objective” but when I tried replacing the “U+FFFE” with ‘ ‘, it does not work and I am still getting the same error.
I have tried these:
- REPLACE(my_column, CHR(65534), ‘ ‘)
- TRANSLATE(my_column, CHR(65534), ‘ ‘)
- REGEXP_REPLACE(my_column, ‘[x{FFFE}]’, ‘ ‘)
- REGEXP_REPLACE(my_column, ‘[^[:print:]]’, ‘ ‘)
1
Use UNISTR to generate the character:
SELECT REPLACE(data, UNISTR('FFFE'), ' ') AS data
FROM table_name;
Which, for the sample data:
CREATE TABLE table_name (data) AS
SELECT TO_CHAR(UNISTR('my shortFFFEterm objective')) FROM DUAL;
Outputs:
| DATA |
|---|
| my short term objective |
fiddle
You can specify 0xFFFE as a unicode character point with u'fffe':
create table my_table(my_column nvarchar2(50))
insert into my_table(my_column) values (unistr('my shortfffeterm objective'));
select dump(my_column, 1016) from my_table
| DUMP(MY_COLUMN,1016) |
|---|
| Typ=1 Len=46 CharacterSet=AL16UTF16: 0,6d,0,79,0,20,0,73,0,68,0,6f,0,72,0,74,ff,fe,0,74,0,65,0,72,0,6d,0,20,0,6f,0,62,0,6a,0,65,0,63,0,74,0,69,0,76,0,65 |
select REPLACE(my_column, u'fffe', ' '), dump(REPLACE(my_column, u'fffe', ' '), 1016)
from my_table
| REPLACE(MY_COLUMN,U’FFFE’,”) | DUMP(REPLACE(MY_COLUMN,U’FFFE’,”),1016) |
|---|---|
| my short term objective | Typ=1 Len=46 CharacterSet=AL16UTF16: 0,6d,0,79,0,20,0,73,0,68,0,6f,0,72,0,74,0,20,0,74,0,65,0,72,0,6d,0,20,0,6f,0,62,0,6a,0,65,0,63,0,74,0,69,0,76,0,65 |
fiddle
However, FFFE is not a valid Unicode character, so it may be that whatever is showing you your current output is substituting something it can’t display with that, and it isn’t really in your string. Possibly you have a character set mismatch somewhere. Use dump() to see what you really have, and if that doesn’t show ff,ee (I would guess it maybe has some type of dash?) then verify the character sets at each level..