OraFAQ Forum: SQL & PL/SQL » Unable to remove hidden character from field

Home » SQL & PL/SQL » SQL & PL/SQL » Unable to remove hidden character from field

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Unable to remove hidden character from field [message #548214]

Tue, 20 March 2012 19:07

rajeevalone
Messages: 11
Registered: March 2012

Junior Member

I have the same problem now..

My database is in UTF8 character set..

And it is not supporting chr(194)||chr(160)

Can you please tell me what character set you were using then ?

And.. is there a way to handle non breaking spaces in UTF8..

Thanks In advance

Report message to a moderator

Re: Unable to remove hidden character from field [message #548215 is a reply to message #548214]

Tue, 20 March 2012 19:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Unable to remove hidden character from field [message #548218 is a reply to message #548214]

Tue, 20 March 2012 19:23

rajeevalone
Messages: 11
Registered: March 2012

Junior Member

CREATE TABLE rjv1(col VARCHAR2(20));

INSERT INTO rjv1 VALUES (chr(194)||chr(160)||chr(82)||chr(88)||chr(66) );

INSERT INTO rjv1 VALUES (chr(194)||chr(160));

select * from rjv1;

SELECT Dump(REPLACE(col,chr(194)||chr(160),' ')) FROM rjv1;

SELECT Dump(col) FROM rjv1;

SELECT * FROM NLS_DATABASE_PARAMETERS where parameter = 'NLS_CHARACTERSET'

Report message to a moderator

Re: Unable to remove hidden character from field [message #548220 is a reply to message #548218]

Tue, 20 March 2012 19:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I do not understand problem or what you expect or desire.

17:57:32 SQL> @t1
17:57:35 SQL> CREATE TABLE rjv1(col VARCHAR2(20));

Table created.

17:57:36 SQL> 
17:57:36 SQL> INSERT INTO rjv1 VALUES (chr(194)||chr(160)||chr(82)||chr(88)||chr(66) );

1 row created.

17:57:36 SQL> 
17:57:36 SQL> INSERT INTO rjv1 VALUES (chr(194)||chr(160));

1 row created.

17:57:36 SQL> 
17:57:36 SQL> select * from rjv1;

COL
--------------------
RXB


17:57:36 SQL> 
17:57:36 SQL> SELECT Dump(REPLACE(col,chr(194)||chr(160),' ')) FROM rjv1;

DUMP(REPLACE(COL,CHR(194)||CHR(160),''))
--------------------------------------------------------------------------------
Typ=1 Len=3: 82,88,66
NULL

17:57:36 SQL> 
17:57:36 SQL> 
17:57:36 SQL> SELECT Dump(col) FROM rjv1;

DUMP(COL)
--------------------------------------------------------------------------------
Typ=1 Len=3: 82,88,66
NULL

17:57:36 SQL> 
17:57:36 SQL> SELECT * FROM NLS_DATABASE_PARAMETERS where parameter = 'NLS_CHARACTERSET';

PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_CHARACTERSET
AL32UTF8

Report message to a moderator

Re: Unable to remove hidden character from field [message #548221 is a reply to message #548220]

Tue, 20 March 2012 20:03

rajeevalone
Messages: 11
Registered: March 2012

Junior Member

when i execute

SELECT Dump(col) FROM rjv1;

before

SELECT Dump(REPLACE(col,chr(194)||chr(160),' ')) FROM rjv1;

Its not showing the 194 and 160 in the output.

Its just taking chr(194)||chr(160) as NULL.

Report message to a moderator

Re: Unable to remove hidden character from field [message #548222 is a reply to message #548221]

Tue, 20 March 2012 20:26

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SELECT CHR (196 USING NCHAR_CS)
FROM DUAL;

CH
--
Ä

manual shows above but my DB does below
http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions026.htm#SQLRF00616

18:18:32 SQL> SELECT CHR (196 USING NCHAR_CS)
FROM DUAL; 18:19:21 2

C
-
A

Report message to a moderator

Re: Unable to remove hidden character from field [message #548226 is a reply to message #548222]

Tue, 20 March 2012 21:07

rajeevalone
Messages: 11
Registered: March 2012

Junior Member

Hmm. The combination of 196 and 160 is non breakable space right..
Is it working fine for you?

Report message to a moderator

Re: Unable to remove hidden character from field [message #548227 is a reply to message #548226]

Tue, 20 March 2012 21:20

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Hmm. The combination of 196 and 160 is non breakable space right..
I don't recognize the term/phrase "non breakable space".

>Is it working fine for you?
no
Take a look at my previous post.
I ran your posted SQL via COPY & PASTE.

Report message to a moderator

Re: Unable to remove hidden character from field [message #548234 is a reply to message #548227]

Wed, 21 March 2012 00:07

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

BlackSwan wrote on Tue, 20 March 2012 19:20

>

I don't recognize the term/phrase "non breakable space".

http://en.wikipedia.org/wiki/Non-breaking_space

Report message to a moderator

Re: Unable to remove hidden character from field [message #548240 is a reply to message #548234]

Wed, 21 March 2012 00:34

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

Try unistr('\00A0') for a non-breaking space, as shown below.

SCOTT@orcl_11gR2> create table test_tab
  2    (id	  number,
  3  	test_col  nvarchar2(60))
  4  /

Table created.

SCOTT@orcl_11gR2> insert into test_tab values
  2    (1, 'this line with 100' || unistr('\00A0') ||
  3  	   'km should not allow a break in 100km.')
  4  /

1 row created.

SCOTT@orcl_11gR2> insert into test_tab values
  2    (2, 'this line with 100' || ' ' ||
  3  	   'km should allow a break in 100km.')
  4  /

1 row created.

SCOTT@orcl_11gR2> column test_col format a18 word_wrapped
SCOTT@orcl_11gR2> select * from test_tab
  2  /

        ID TEST_COL
---------- ------------------
         1 this line with
           100 km should not
           allow a break in
           100km.

         2 this line with 100
           km should allow a
           break in 100km.


2 rows selected.

Report message to a moderator

Re: Unable to remove hidden character from field [message #548241 is a reply to message #548240]

Wed, 21 March 2012 00:37

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

Perhaps I am not understanding the problem. Are you trying to figure out what to use for a non-breaking space? Or do you have some chr(196) || chr (160) that you are trying to replace with a visible space and cannot?

Report message to a moderator

Re: Unable to remove hidden character from field [message #548251 is a reply to message #548241]

Wed, 21 March 2012 01:42

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

It looks like this thread may have been split from the following thread:

http://www.orafaq.com/forum/t/168601/0/

Report message to a moderator

Re: Unable to remove hidden character from field [message #548369 is a reply to message #548241]

Wed, 21 March 2012 11:20

rajeevalone
Messages: 11
Registered: March 2012

Junior Member

Barbara Boehmer wrote on Wed, 21 March 2012 00:37

Perhaps I am not understanding the problem. Are you trying to figure out what to use for a non-breaking space? Or do you have some chr(196) || chr (160) that you are trying to replace with a visible space and cannot?

Yes..Actually i have to find out the non breaking space in a column and either remove it or replace it with a regular space ..

That's my exact problem.

Report message to a moderator

Re: Unable to remove hidden character from field [message #548370 is a reply to message #548369]

Wed, 21 March 2012 11:28

rajeevalone
Messages: 11
Registered: March 2012

Junior Member

when am using this command

INSERT INTO rjv1 VALUES (chr(82)||chr(88)|| unistr('\00A0')||chr(66) );

and then

SELECT Dump(col) FROM rjv1; then am getting the output as:

Typ=1 Len=5: 82,88,194,160,66 (which is perfect!)

Now, How to replace this character with a regular space..

I tried this command SELECT Dump(REPLACE(col,chr(194)||chr(160),' ')) FROM rjv1;

But no luck Sad

Sad

Still SELECT Dump(col) FROM rjv1;

shows the same output

Typ=1 Len=5: 82,88,194,160,66

Report message to a moderator

Re: Unable to remove hidden character from field [message #548371 is a reply to message #548370]

Wed, 21 March 2012 11:35

rajeevalone
Messages: 11
Registered: March 2012

Junior Member

SELECT Dump(REPLACE(col,unistr('\00A0'),' ')) FROM rjv1;

is changing the output to Typ=1 Len=4: 82,88,32,66

But its not saving it .. i even tried executing commit ..it dint work Sad

Sad

Report message to a moderator

Re: Unable to remove hidden character from field [message #548372 is a reply to message #548371]

Wed, 21 March 2012 11:52

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

does the follow help?

09:58:00 SQL> @rjv1
09:58:07 SQL> INSERT INTO rjv1 VALUES (chr(82)||chr(88)|| unistr('\00A0')||chr(66) );

1 row created.

09:58:07 SQL> SELECT Dump(col) FROM rjv1;

DUMP(COL)
--------------------------------------------------------------------------------
Typ=1 Len=5: 82,88,194,160,66

09:58:07 SQL> SELECT Dump(REPLACE(col,unistr('\00A0'),' ')) FROM rjv1;

DUMP(REPLACE(COL,UNISTR('\00A0'),''))
--------------------------------------------------------------------------------
Typ=1 Len=4: 82,88,32,66

09:58:07 SQL> SELECT Dump(col) FROM rjv1;

DUMP(COL)
--------------------------------------------------------------------------------
Typ=1 Len=5: 82,88,194,160,66

09:58:07 SQL> update rjv1 set col = REPLACE(col,unistr('\00A0'),' ');

1 row updated.

09:58:07 SQL> SELECT Dump(col) FROM rjv1;

DUMP(COL)
--------------------------------------------------------------------------------
Typ=1 Len=4: 82,88,32,66

09:58:07 SQL>

[Updated on: Wed, 21 March 2012 11:58]

Report message to a moderator

Re: Unable to remove hidden character from field [message #548373 is a reply to message #548372]

Wed, 21 March 2012 11:58

rajeevalone
Messages: 11
Registered: March 2012

Junior Member

Perfect! Thank You! :) Smile

Smile

Report message to a moderator

Previous Topic:	Extract data into excel problem
Next Topic:	How to solve the problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Aug 03 07:12:23 CDT 2025