Home » SQL & PL/SQL » SQL & PL/SQL » Replacing Special Character
Replacing Special Character [message #300209] Thu, 14 February 2008 10:47 Go to next message
world.apps
Messages: 70
Registered: January 2007
Location: Hyderabad
Member
Hi,

I have special symbol in my output. I am trying to replace it with null I am using following SQL statement

My data base column value is HH Dunning Letter Set 1
SQL> select translate('HH Dunning Letter Set 1', ' 1',' ')   
     from dual;
 Output is: HH Dunning Letter Set 

But when I use this with my database column name it is not giving the result.

My SQL is
SQL> select translate(name, ' 1',' ')   
     from dunning_sets;

HH Dunning Letter Set 1 value is there in Name column of dunning_sets table.

But my output is:
HH Dunning Letter Set 1

I have found in my application they have given name like HH Dunning Letter Set-1 but in database it is showing like this
HH Dunning Letter Set 1

Pls help how to solve this.

Thanks in advance

[Updated on: Thu, 14 February 2008 10:50] by Moderator

Report message to a moderator

Re: Replacing Special Character [message #300213 is a reply to message #300209] Thu, 14 February 2008 10:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The most likely is that is not in the name but you see it when you query because your NLS_LANG is set to a value that does not understand the character and DISPLAY instead.

What is your database character set, your client character set (nls_database_parameters, v$nls_parameters)?

Regards
Michel
Re: Replacing Special Character [message #300222 is a reply to message #300213] Thu, 14 February 2008 11:22 Go to previous messageGo to next message
world.apps
Messages: 70
Registered: January 2007
Location: Hyderabad
Member
Hi,

In v$nls_parameters we have NLS_CHARACTERSET value is UTF8.

Thanks
Re: Replacing Special Character [message #300234 is a reply to message #300222] Thu, 14 February 2008 11:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Both values, please.

And also:
select name, dump(name) from ... where ...
from SQL*Plus

Regards
Michel
Re: Replacing Special Character [message #300260 is a reply to message #300209] Thu, 14 February 2008 15:14 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
This might help, we use it all the time for cleaning text fields imported from dodgy sources like mainframe filesystems, where the data load is a "best guess" approximation of the data file.

It removes un-printable characters.


select regexp_replace(string,'[^[:print:]]',null)
from dual;

[Updated on: Thu, 14 February 2008 15:14]

Report message to a moderator

Re: Replacing Special Character [message #300301 is a reply to message #300260] Thu, 14 February 2008 23:14 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The problem is not there.

Regards
Michel
Previous Topic: How to find last three letter of a word
Next Topic: Is this a bug or a "feature"
Goto Forum:
  


Current Time: Sat Dec 10 12:45:22 CST 2016

Total time taken to generate the page: 0.05568 seconds