Skip to Main Content
  • Questions

  • Replacing text with extended ascii characters

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Abhishek.

Asked: May 02, 2016 - 6:35 am UTC

Last updated: July 05, 2019 - 5:37 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi Tom,
I have a column which contains text like [DEG],[MICRO],[PHASE]. I want to replace it with their symbols in oracle. My current version of oracle in 11g. Below link contains the complete list of replacements.

http://i.imgur.com/bl7xvZJ.png

I tried to do something like this in toad.
SELECT REPLACE(english_descr,
               '[DEG]',
               (UNISTR(CHR(167))))
  FROM test2;


It replaced all the [DEG] with question marks.

Any help would be appreciated.

and Connor said...

You SQL is probably fine - it all depends on the server and client characterset.

For example, take a look here

SPECIAL_CHARS

See on SQL Developer, it works fine (because the NLS settings are correctly specified on my laptop).

But even with correct settings, notice how my MS-DOS window on the same box, cannot display the copywright symbol...it simply does not exist in the MS-DOS environment.

So firstly, try it in SQL Developer. If that does not work, then you need to look at your server/client character set.

See the Globalization guide for details on charactsets

http://docs.oracle.com/database/121/NLSPG/toc.htm


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Symbols and character set

Sergiusz, November 30, 2016 - 6:58 am UTC

1. UNISTR(CHR(167))

This expression makes little sense. To encode the degree symbol, use UNISTR('\00b0'). CHR(167) is actually the section sign symbol (§) in WE8MSWIN1252.

2. For the replacement to work, the character set of the english_descr column must support the desired characters. If english_descr is VARCHAR2/CHAR/CLOB, the database character set must be one of the Windows code pages or Unicode (AL32UTF8). If english_descr is NVARCHAR2/NCHAR/NCLOB, the national character set is always Unicode (AL16UTF61 or UTF8), so the symbols are supported.

3. Depending on the data type of english_descr, the client must be able to read the column without data loss due to character set conversion. SQL Developer can do this.



Connor McDonald
December 01, 2016 - 12:47 am UTC

nice input
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database