?
Solved

PLSQL- replace is not working for â¿¿

Posted on 2014-10-21
12
Medium Priority
?
268 Views
Last Modified: 2014-10-24
hi experts:

i am trying this
UPDATE ta_cuenta_contable
SET nombrecuentacontable   = REPLACE(nombrecuentacontable ,'â¿¿','-');
commit;

the data is
Participaciones de los trabajadores diferidas â¿¿ Pa

but it is not working
0
Comment
Question by:enrique_aeo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 total points
ID: 40395970
Seems to work for me:
SQL> select REPLACE('Participaciones de los trabajadores diferidas â¿¿ Pa','â¿¿','-') from dual;

REPLACE('PARTICIPACIONESDELOSTRABAJADORESDIFERIDAS
--------------------------------------------------
Participaciones de los trabajadores diferidas - Pa

Open in new window


The upside down question mark is typically some non-printable character for Oracle.  Make sure what you have in the data is exactly the character you are trying to replace.

What are you getting?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40395972
You can check the values with:
select dump('diferidas â¿¿ Pa') from dual;

Do the same with the string you are wanting to replace and make sure they are the same.
0
 

Author Comment

by:enrique_aeo
ID: 40395980
this is result

select dump('diferidas â¿¿ Pa') from dual;

Typ=96 Len=16: 100,105,102,101,114,105,100,97,115,32,226,191,191,32,80,97
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:enrique_aeo
ID: 40395986
you can send the code to the UPDATE
0
 

Author Comment

by:enrique_aeo
ID: 40395993
I EXECUTE THIS
select REPLACE('Participaciones de los trabajadores diferidas â¿¿ Pa','â¿¿','-') from dual;

the result
Participaciones de los trabajadores diferidas - Pa
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 total points
ID: 40396022
>>Typ=96 Len=16: 100,105,102,101,114,105,100,97,115,32,226,191,191,32,80,97

That is that I got.  I actually meant for you to DUMP the actual column value in the table you have.  Remember the upside down question marks may just be unprintable characters.

If the replace works in a SELECT, it will work in an UPDATE.

Since you state it doesn't work, then the data you have in the table doesn't match the data you have in the replace string.

Check the actual data in the table and NOT what we have been copying and pasting.
0
 

Author Comment

by:enrique_aeo
ID: 40396028
is not working

UPDATE ta_cuenta_contable
   SET nombrecuentacontable = REPLACE(nombrecuentacontable,
                                      'Typ=96 Len=16: 100,105,102,101,114,105,100,97,115,32,226,191,191,32,80,97',
                                      '-');
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 total points
ID: 40396051
You do not put the dump string in the replace.

You figure out what characters you actually have in the data and make sure you have those same characters in the replace string.

The DUMP shows what the actual data is.

What does this return:
select nombrecuentacontable, dump(nombrecuentacontable ) from ta_cuenta_contable
where nombrecuentacontable like 'Participaciones de los trabajadores%';
0
 

Author Comment

by:enrique_aeo
ID: 40400743
this result
NOMBRECUENTACONTABLE      DUMP(NOMBRECUENTACONTABLE)
Participaciones de los trabajadores diferidas – Pa      Typ=1 Len=52: 80,97,114,116,105,99,105,112,97,99,105,111,110,101,115,32,100,101,32,108,111,115,32,116,114,97,98,97,106,97,100,111,114,101,115,32,100,105,102,101,114,105,100,97,115,32,226,128,147,32,80,97
Participaciones de los trabajadores diferidas – Pa      Typ=1 Len=52: 80,97,114,116,105,99,105,112,97,99,105,111,110,101,115,32,100,101,32,108,111,115,32,116,114,97,98,97,106,97,100,111,114,101,115,32,100,105,102,101,114,105,100,97,115,32,226,128,147,32,80,97
Participaciones de los trabajadores diferidas – Pa      Typ=1 Len=52: 80,97,114,116,105,99,105,112,97,99,105,111,110,101,115,32,100,101,32,108,111,115,32,116,114,97,98,97,106,97,100,111,114,101,115,32,100,105,102,101,114,105,100,97,115,32,226,128,147,32,80,97
Participaciones de los trabajadores diferidas – Pa      Typ=1 Len=52: 80,97,114,116,105,99,105,112,97,99,105,111,110,101,115,32,100,101,32,108,111,115,32,116,114,97,98,97,106,97,100,111,114,101,115,32,100,105,102,101,114,105,100,97,115,32,226,128,147,32,80,97
Participaciones de los trabajadores diferidas – Pa      Typ=1 Len=52: 80,97,114,116,105,99,105,112,97,99,105,111,110,101,115,32,100,101,32,108,111,115,32,116,114,97,98,97,106,97,100,111,114,101,115,32,100,105,102,101,114,105,100,97,115,32,226,128,147,32,80,97
Participaciones de los trabajadores diferidas – Pa      Typ=1 Len=52: 80,97,114,116,105,99,105,112,97,99,105,111,110,101,115,32,100,101,32,108,111,115,32,116,114,97,98,97,106,97,100,111,114,101,115,32,100,105,102,101,114,105,100,97,115,32,226,128,147,32,80,97
Participaciones de los trabajadores diferidas – Re      Typ=1 Len=52: 80,97,114,116,105,99,105,112,97,99,105,111,110,101,115,32,100,101,32,108,111,115,32,116,114,97,98,97,106,97,100,111,114,101,115,32,100,105,102,101,114,105,100,97,115,32,226,128,147,32,82,101
Participaciones de los trabajadores diferidas – Re      Typ=1 Len=52: 80,97,114,116,105,99,105,112,97,99,105,111,110,101,115,32,100,101,32,108,111,115,32,116,114,97,98,97,106,97,100,111,114,101,115,32,100,105,102,101,114,105,100,97,115,32,226,128,147,32,82,101
Participaciones de los trabajadores diferidas – Re      Typ=1 Len=52: 80,97,114,116,105,99,105,112,97,99,105,111,110,101,115,32,100,101,32,108,111,115,32,116,114,97,98,97,106,97,100,111,114,101,115,32,100,105,102,101,114,105,100,97,115,32,226,128,147,32,82,101
Participaciones de los trabajadores diferidas – Re      Typ=1 Len=52: 80,97,114,116,105,99,105,112,97,99,105,111,110,101,115,32,100,101,32,108,111,115,32,116,114,97,98,97,106,97,100,111,114,101,115,32,100,105,102,101,114,105,100,97,115,32,226,128,147,32,82,101
Participaciones de los trabajadores diferidas – Re      Typ=1 Len=52: 80,97,114,116,105,99,105,112,97,99,105,111,110,101,115,32,100,101,32,108,111,115,32,116,114,97,98,97,106,97,100,111,114,101,115,32,100,105,102,101,114,105,100,97,115,32,226,128,147,32,82,101
Participaciones de los trabajadores diferidas – Re      Typ=1 Len=52: 80,97,114,116,105,99,105,112,97,99,105,111,110,101,115,32,100,101,32,108,111,115,32,116,114,97,98,97,106,97,100,111,114,101,115,32,100,105,102,101,114,105,100,97,115,32,226,128,147,32,82,101
result-DUMP.txt
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 total points
ID: 40400757
Cool.

Now DUMP the string used in your replace statement.  Make sure the values match.

select dump('<your replace string>') from dual;

where <your replace string> is the actual characters in bold below:
REPLACE(nombrecuentacontable ,'â¿¿','-');

It looks like the characters you want to replace have the dumped values: 226,128,147
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40400798
The upside down question marks tells me the editor/program/??? you are using doesn't recognize the characters.

If the dumps are accurate, try the replace with the values instead of the characters.

Try this:
select REPLACE(nombrecuentacontable ,chr(226)||chr(128)||chr(147),'-')
from ta_cuenta_contable;
0
 

Author Comment

by:enrique_aeo
ID: 40402315
select nombrecuentacontable, REPLACE(nombrecuentacontable ,chr(226)||chr(128)||chr(147),'-')
from ta_cuenta_contable
where nombrecuentacontable like 'Participaciones de los trabajadores%';

Participaciones de los trabajadores diferidas – Pa      Participaciones de los trabajadores diferidas - Pa
Participaciones de los trabajadores diferidas – Pa      Participaciones de los trabajadores diferidas - Pa
Participaciones de los trabajadores diferidas – Pa      Participaciones de los trabajadores diferidas - Pa
Participaciones de los trabajadores diferidas – Pa      Participaciones de los trabajadores diferidas - Pa
Participaciones de los trabajadores diferidas – Pa      Participaciones de los trabajadores diferidas - Pa
Participaciones de los trabajadores diferidas – Pa      Participaciones de los trabajadores diferidas - Pa
Participaciones de los trabajadores diferidas – Re      Participaciones de los trabajadores diferidas - Re
Participaciones de los trabajadores diferidas – Re      Participaciones de los trabajadores diferidas - Re
Participaciones de los trabajadores diferidas – Re      Participaciones de los trabajadores diferidas - Re
Participaciones de los trabajadores diferidas – Re      Participaciones de los trabajadores diferidas - Re
Participaciones de los trabajadores diferidas – Re      Participaciones de los trabajadores diferidas - Re
Participaciones de los trabajadores diferidas – Re      Participaciones de los trabajadores diferidas - Re

i use
update ta_cuenta_contable
set nombrecuentacontable = REPLACE(nombrecuentacontable ,chr(226)||chr(128)||chr(147),'-')
where nombrecuentacontable like 'Participaciones de los trabajadores%';

This OK. Thanks
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question