PLSQL- replace is not working for â¿¿

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
enrique_aeoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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
slightwv (䄆 Netminder) Commented:
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
enrique_aeoAuthor Commented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

enrique_aeoAuthor Commented:
you can send the code to the UPDATE
0
enrique_aeoAuthor Commented:
I EXECUTE THIS
select REPLACE('Participaciones de los trabajadores diferidas â¿¿ Pa','â¿¿','-') from dual;

the result
Participaciones de los trabajadores diferidas - Pa
0
slightwv (䄆 Netminder) Commented:
>>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
enrique_aeoAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
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
enrique_aeoAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
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
slightwv (䄆 Netminder) Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
enrique_aeoAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.