[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to create a view ignoring data with foreign characters (Chinese)

Posted on 2011-10-06
14
Medium Priority
?
408 Views
Last Modified: 2012-06-27
Hello Experts,

We have to create a view or process a table from tables than contains mixed western european and chinese characters.

We do not want to remove the chinese characters from the data itself, we just want to blank them for an specific query / view.

The most general approach will be the best as today it is a problem with chinese characters and in the future with other languages as arabic.

The chinese characters are sometimes inside columns with westerna european chacarters. We just want to ignore the chinese chacters.

We have looked at regular expression, translate but we are struggling to make this work.

















0
Comment
Question by:FABRICIO76
  • 8
  • 4
  • 2
14 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36924496
I don't have direct access to any database where I can set up a multi-byte table to test this but you mention you experimented with regular expressions from what was provided in your other question.

What have you tried?  I'm thinking a regexp_replace should do this.

I'm thinking regexp_replace(your_column,'[^[:alnum:]]',' ')

If you can provide some sample data (assuming the chinese characters will upload to the site properly), I can try to load it in some of my test systems


0
 

Author Comment

by:FABRICIO76
ID: 36924514
Hi, let me try this.

0
 

Author Comment

by:FABRICIO76
ID: 36924839
Hi there,

I am applying what you have suggested as follows:

select regexp_replace(first,'[^[:alnum:]]',' ') from table_name where name_idfr=23714

However this still returns the chinese characters as you can see below:

¿¿ ¿¿ Martin
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36924860
I was afraid the characters wouldn't post right.  Can you upload a file with some sample data?

I doubt I can load them into any of my databases but I'll try until another Expert comes along that might be better with working with multi-byte character sets.
0
 

Author Comment

by:FABRICIO76
ID: 36924896
Here we go, this is output and the chinese character remains on the output

 Select Output
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36924923
I actually meant a data sample not a picture.  I would like to actually try an load it into a database on my end and experiment.
0
 

Author Comment

by:FABRICIO76
ID: 36924979
0
 

Author Comment

by:FABRICIO76
ID: 36924986
Here we go, you can see that two colums contains chinese characters.

The issue is actually much bigger and affects several tables, etc but this is a test example that shows exactly what the issue is.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36925106
Thanks for the sample.  Unfortunately as I expected, I don't have any UTF databases where I can load it.

Hopefully an Expert will be along soon that can use the sample.

What are your 'allowable' characters?  If just a-z, A-Z, 0-9 and a space:
regexp_replace(column,'[^a-zA-Z0-9 ]','')
0
 

Author Comment

by:FABRICIO76
ID: 36925153
The external system is IBM based and is running the following caharecter data set: WE8EBCDIC284

Well this is the Oracle and the external converts to EBCDIC 284

Basically Spanish & Latin American Spanish.

But, even we could more or less easily fix this by exporting to a determined character set this is not an acceptabkle solution as the chinese are converted to little squeares and the external system will reject it.

So basically this is why we need to black them out when querying.

I hope that makes sense.

Kindest regards
0
 
LVL 15

Accepted Solution

by:
Franck Pachot earned 2000 total points
ID: 36925154
Hi,
You can use the convert function to convert all characters to the western european  character set. Other characters will be shown as inverted question mark.
See: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions027.htm
Regards,
Franck.
0
 

Author Comment

by:FABRICIO76
ID: 36925177
Hello Franck,

Problem is this is not accepted as a solution, the data has to be exported to a third party system and the request is to blank the characters.

Question marks could process as they are supported in the external system character set but not a solution that is acceptable at this time.

Any other ideas?

I will test your recommendation anyway just in case

0
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 36926438
Just user replace function to translate inverted interrogation marks to what you want.
0
 

Author Closing Comment

by:FABRICIO76
ID: 36929795
Convert First and then Replace worked
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

873 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