sql developer umlaut charectes

hi experts,

i am executing the below query on my DB to replace the junk charecters with meaningful german characters but it gives null results,  can u plz help??

query -

select NAME_1, TRANSLATE(TRIM(NAME_1),'{|}[]~',chr(228)||chr(246)||chr(252)||chr(196)||chr(220)||chr(214)||chr(223)) AS "DEALER_NAME"
from T_PARTY;

results expected -

Patentanw{lte     -------->>>>    Patentanwälte

{this gets tranalated to ä

i hope i have made the problem clear enough.
let me know if more details should be given here.
waiting for ur suggestions.
thanks
pals cDataBase learnerAsked:
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.

lcohanDatabase AnalystCommented:
Please try code below:

declare @stringtoparse nvarchar(max)
set @stringtoparse = 'Patentanw{lte'
set @stringtoparse = REPLACE(@stringtoparse, '{', 'ä')
print (@stringtoparse);


for your speciffic case I guess would be like:

select NAME_1, REPLACE(NAME_1, '{', 'ä') AS DEALER_NAME
from T_PARTY;
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
slightwv (䄆 Netminder) Commented:
What database?  SQL Developer implies Oracle but I don't want to guess.

Are you sure the data in the database is bad?  It might be a character set issue on the client side when you query it.

>>but it gives null results,  

You shouldn't get a null unless the input is null.  Can you post a screenshot of what you are actually seeing?
0
pals cDataBase learnerAuthor Commented:
Thanks for ur comment. however i need all such charecters converted and not just one.. hence the different chr() in the query.. else i need to execute for each column each character this query for many tables which would be very time consuming.  please suggest
0
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

pals cDataBase learnerAuthor Commented:
also i need to use this function inside my very big migration script which pulls data from one DB and loads into a new one for a dozen tables..
0
slightwv (䄆 Netminder) Commented:
Here is my test using SQL Developer:
Untitled.jpg
0
pals cDataBase learnerAuthor Commented:
did u use the same query i have pasted here or any changes?
0
pals cDataBase learnerAuthor Commented:
when i do it, it gives me null value.. any idea.. y? any settings in the sql developer?
0
slightwv (䄆 Netminder) Commented:
First:  Can you confirm you are using Oracle?

>>did u use the same query i have pasted here or any changes?

Look at it?  I copied and pasted your query and added my own WITH clause to simulate your table.

>>when i do it, it gives me null value.. any idea.. y? any settings in the sql developer?

Here is my actual query I used as a test.  Confirm you can run it as-is and get data:
with t_party as (
select 'Patentanw{lte' as name_1 from dual
)
select NAME_1, TRANSLATE(TRIM(NAME_1),'{|}[]~',chr(228)||chr(246)||chr(252)||chr(196)||chr(220)||chr(214)||chr(223)) AS "DEALER_NAME"
from T_PARTY;

Open in new window


If you get data with my query, then you likely have a data or character set problem somewhere.
0
pals cDataBase learnerAuthor Commented:
okay. let me try this and get back to u soon.
0
pals cDataBase learnerAuthor Commented:
yes i tried it and this query also gives me null results.
i have also observed that the charecter sets are different than the source db.
the new db has Al32utf8 while the old one has we8iso8859p15..
what do i do in this case?
0
slightwv (䄆 Netminder) Commented:
I'm not sure exactly what characters are allowed in we8iso8859p15 but were you connected to the new database when you ran my query?  Let's eliminate that.

Can you post the screen shot where you ran my example from dual?

What version of SQL Developer are you using?  Can you run the latest?  

18.,2 is available:
https://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
0
pals cDataBase learnerAuthor Commented:
i executed another translate statement and it gave me required results. thanks for all the help!!
0
slightwv (䄆 Netminder) Commented:
I'm curious what it was and what caused the one I posted to not work for you when it did for me.  Can you share what worked for you?
0
pals cDataBase learnerAuthor Commented:
the translate in the query i changed to this -
translate(col,'[]({','äëöü')
and then it worked.. the charecters above are not exact but this is how it got resolved.. the problem was that the charecter sets were different in both the DBs and hence the need for conversions. hope i answered ur que completely.
0
slightwv (䄆 Netminder) Commented:
As long as it works and you are happy, I'm OK.  Feel free to accept your answer as the solution.
1
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
SQL

From novice to tech pro — start learning today.