Home » SQL & PL/SQL » SQL & PL/SQL » REPLACE not replacing
REPLACE not replacing [message #584688] Thu, 16 May 2013 16:08 Go to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Does anybody have any idea why the REPLACE function is not replacing. I assume it has something to do with the ASCII value being zero.

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
Connected as aggs@AGGSTEST
 
SQL> 
SQL> SELECT str,
  2         e9,
  3         REPLACE(str, '%E9', e9) replace,
  4         regexp_replace(str, '%E9', e9) regexp_replace,
  5         utl_url.unescape(str, 'UTF8') utl_url,
  6         ascii(e9) ascii
  7    FROM (SELECT 'Soir%E9e' str,
  8                 chr(to_number('E9', 'xx')) e9
  9            FROM dual);
 
STR      E9  REPLACE REGEXP_REPLACE UTL_URL ASCII
-------- --- ------- -------------- ------- -----
Soir%E9e é   Soire   Soirée         Soirée      0
 
Re: REPLACE not replacing [message #584691 is a reply to message #584688] Thu, 16 May 2013 17:01 Go to previous messageGo to next message
spacebar
Messages: 26
Registered: February 2007
Junior Member
Try it like this:
SQL> select str, e9, replace( str, '%E9', e9 ) as replace,
  2         regexp_replace( str, '%E9', e9   ) as regexp_replace,
  3         utl_url.unescape( str ) as utl_url,
  4         ascii( e9 ) as ascii
  5   from ( select 'Soir%E9e' as str, chr( to_number( 'E9', 'xx' ) ) as e9
  6            from dual );

str        e9         replace    regexp_replace utl_url    ascii
---------- ---------- ---------- -------------- ---------- -----
Soir%E9e   é          Soirée     Soirée         Soiree       233
Re: REPLACE not replacing [message #584694 is a reply to message #584691] Thu, 16 May 2013 17:50 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Really? You think the format is going to matter?

Well, at least you've demonstrated that it does work on some instances. It's looking like a DB configuration issue. Your utl_url.unescape doesn't work though. Anybody have any guesses?

[Updated on: Thu, 16 May 2013 18:43]

Report message to a moderator

Re: REPLACE not replacing [message #584704 is a reply to message #584688] Fri, 17 May 2013 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 59122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think it has something to do with your NLS_LANG and client OS character set.

In 10.2 Windows, character set 1252 for both OS and NLS_LANG:
SQL> SELECT str,
  2         e9,
  3         REPLACE(str, '%E9', e9) replace,
  4         regexp_replace(str, '%E9', e9) regexp_replace,
  5  --       utl_url.unescape(str, 'UTF8') utl_url,
  6         ascii(e9) ascii
  7  FROM (SELECT 'Soir%E9e' str,
  8               chr(to_number('E9', 'xx')) e9
  9        FROM dual);
STR      E REPLACE  REGEXP_REPLACE       ASCII
-------- - -------- --------------- ----------
Soir%E9e é Soirée   Soirée                 233


In 11.2, DOS box:
SQL> host set NLS_LANG
NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

SQL> host chcp
Page de codes active : 850

SQL> SELECT str,
  2         e9,
  3         REPLACE(str, '%E9', e9) replace,
  4         regexp_replace(str, '%E9', e9) regexp_replace,
  5  --       utl_url.unescape(str, 'UTF8') utl_url,
  6         ascii(e9) ascii
  7  FROM (SELECT 'Soir%E9e' str,
  8               chr(to_number('E9', 'xx')) e9
  9        FROM dual);
STR      E REPLACE  REGEXP_REPLACE       ASCII
-------- - -------- --------------- ----------
Soir%E9e Ú SoirÚe   SoirÚe                 233

Changing client code page (character set):
C:\>chcp 1252
Page de codes active : 1252
C:\> sqlplus michel/michel

SQL*Plus: Release 11.2.0.1.0 Production on Ven. Mai 17 07:41:54 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing

SQL> SELECT str,
  2         e9,
  3         REPLACE(str, '%E9', e9) replace,
  4         regexp_replace(str, '%E9', e9) regexp_replace,
  5  --       utl_url.unescape(str, 'UTF8') utl_url,
  6         ascii(e9) ascii
  7  FROM (SELECT 'Soir%E9e' str,
  8               chr(to_number('E9', 'xx')) e9
  9        FROM dual);
STR      E REPLACE  REGEXP_REPLACE       ASCII
-------- - -------- --------------- ----------
Soir%E9e é Soirée   Soirée                 233

11.2, DOS, changing NLS_LANG (original code page):
C:\>chcp 850
Page de codes active : 850

C:\>set NLS_LANG=AMERICAN_AMERICA.WE8PC850

C:\>sqlplus michel/michel

SQL*Plus: Release 11.2.0.1.0 Production on Ven. Mai 17 07:44:53 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing

SQL> col regexp_replace format a15
SQL> SELECT str,
  2         e9,
  3         REPLACE(str, '%E9', e9) replace,
  4         regexp_replace(str, '%E9', e9) regexp_replace,
  5  --       utl_url.unescape(str, 'UTF8') utl_url,
  6         ascii(e9) ascii
  7  FROM (SELECT 'Soir%E9e' str,
  8               chr(to_number('E9', 'xx')) e9
  9        FROM dual);
STR      E REPLACE  REGEXP_REPLACE       ASCII
-------- - -------- --------------- ----------
Soir%E9e é Soirée   Soirée                 233

In short, there is a mismatch between your OS code page (character set) and your NLS_LANG.

Regards
Michel
Re: REPLACE not replacing [message #584708 is a reply to message #584694] Fri, 17 May 2013 00:55 Go to previous messageGo to next message
sss111ind
Messages: 480
Registered: April 2012
Location: India
Senior Member

For me it is also working as the same way what suggested by SpaceBar.
Re: REPLACE not replacing [message #584709 is a reply to message #584704] Fri, 17 May 2013 00:59 Go to previous message
Michel Cadot
Messages: 59122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also maybe your character set has no character with code E9.
Which ones are they (client OS, NLS_LANG, database)?

Regards
Michel

[Updated on: Fri, 17 May 2013 01:00]

Report message to a moderator

Previous Topic: DBLINK, DBMS_AQADM, STOP/START QUEUE
Next Topic: Ref cursor use
Goto Forum:
  


Current Time: Thu Sep 18 05:42:01 CDT 2014

Total time taken to generate the page: 0.13510 seconds