3

I am trying to remove certain characters from a VARCHAR2 using translate. Characters 160 (some kind of space) and 243 (paragraph control character?), however, appear to be "phantom" characters that are undetectable by both INSTR and TRANSLATE. LENGTH works, but only if it's the only character in a string. LENGTH(CHR(160)) returns 1, but LENGTH(CHR(160) || CHR(110)) also returns 1 when you'd think it would return 2. I've found that REPLACE works in stripping these phantom characters from a string, but I like translate better because it's easier to read and maintain whereas a long nesting or REPLACE functions is just cumbersome.

Is there some other way to strip these characters from a VARCHAR2 without using replace?

EDIT: It appears that character 243 elsewhere registers as ≤. However, Oracle has no problem displaying this character when I selected it explicitly. When I select CHR(243), it just displays the block replacement character. Plus, this source points 243 to the paragraph character which makes more sense since that's a control code.

oscilatingcretin
  • 10,457
  • 39
  • 119
  • 206
  • 1
    `ASCIISTR()` could strip out them.. – Maheswaran Ravisankar Mar 06 '14 at 13:32
  • @OracleUser This is actually getting closer. However, it doesn't strip unsupported characters, but instead converts them to `\FFFD\FFFD`. If it can convert them, it stands to reason that it could also remove them. I can't use a regex in case `\FFFD` is ever a valid string in the column. – oscilatingcretin Mar 06 '14 at 13:41
  • Can you give me `dump(your_column)` for a sample ? – Maheswaran Ravisankar Mar 06 '14 at 13:43
  • Here's an example: select dump('沈阳市' || chr(160) || chr(243) || chr(15712189) || chr(15556979) || chr(15300713)) from dual; I need to also support Kanji characters, so I don't know if ASCIISTR will work. – oscilatingcretin Mar 06 '14 at 13:49
  • select length(chr(160)||chr(110)) from dual; Returns 2 in my database. – Rene Mar 12 '14 at 09:20

1 Answers1

0

What about using a regular expression? This removes your trouble characters by replacing char 160 or 243 with nothing:

SQL> select regexp_replace('abc' || chr(160) || chr(243) || 'def', '(' || chr(160) || '|' || chr(243) || ')', '') from dual;

REGEXP
------
abcdef

SQL>
Gary_W
  • 9,933
  • 1
  • 22
  • 40