Home » SQL & PL/SQL » SQL & PL/SQL » How to identify Unicode Characters (Oracle 10g)
How to identify Unicode Characters [message #411882] Tue, 07 July 2009 04:18 Go to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

Hi Experts

I am migrating the data from Oracle 10 g table to SQL SERVER 2005 through SSIS (Microsoft tecniologies) but while migrating the data from data type NCLOB to text datatype in SQL SERVER 2005 through SSIS, it breaks becasue of Unicode character.
It means the Character is not supported in SQL SERVER 2005.

My problem is to find out that character (Not supported in SQL SERVER 2005) and remove that character from the NCLOB column.

Please suggest how to achieve it!
Re: How to identify Unicode Characters [message #411886 is a reply to message #411882] Tue, 07 July 2009 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It means the Character is not supported in SQL SERVER 2005.

What are the characters not supported?

Regards
Michel
Re: How to identify Unicode Characters [message #411889 is a reply to message #411886] Tue, 07 July 2009 04:35 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

Hi Michel

Below are the Unicode characters which are not supported in SQL SERVER 2005 (before the comma) . And while migration we convert those with the adjacent Non-Unicode character (after the comma).

(unicode,non-Unicode)

('fi','fi')

('―','-')

('●','*')

('η','n')

Please let me know if anything else you require.
Re: How to identify Unicode Characters [message #411898 is a reply to message #411889] Tue, 07 July 2009 04:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use TRANSLATE function to convert any not supported character to one supported.

Regards
Michel
Re: How to identify Unicode Characters [message #411908 is a reply to message #411886] Tue, 07 July 2009 05:12 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

But before that how to identify the other characters at run time which are not supported in SQL SERVER 2005 apart from the one which I already mention.
Re: How to identify Unicode Characters [message #411933 is a reply to message #411908] Tue, 07 July 2009 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ask SQL SERVER which characters are supported then use TRANSLATE function to find those that are not in this list.

Regards
Michel

[Updated on: Tue, 07 July 2009 05:48]

Report message to a moderator

Re: How to identify Unicode Characters [message #411975 is a reply to message #411882] Tue, 07 July 2009 06:31 Go to previous messageGo to next message
deepfat
Messages: 1
Registered: July 2009
Location: UK
Junior Member

You want to take the errors out of the data transform tasks to a separate file and not into the SQL Server table. Then you cna see what's giving you grief. afryer@microsoft.com
Re: How to identify Unicode Characters [message #411997 is a reply to message #411975] Tue, 07 July 2009 07:11 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

Thanks all for quick reply!

I have come to know 1 function which identify the Unicode characters i.e. ASCIISTR.
Re: How to identify Unicode Characters [message #412208 is a reply to message #411933] Wed, 08 July 2009 04:10 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

Hi Michel

I applied the fucntion ASCIISTR on a string and if i see the output then only I can find the unicode character (the one which is replaced with the code). But i want to Replace this code with the space.

Please help!
Re: How to identify Unicode Characters [message #412407 is a reply to message #411882] Thu, 09 July 2009 03:35 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

Hi All


I applied the fucntion ASCIISTR to get the Special Characters in a text but in this process this function includes the characters which are accepted by SQL SERVER 2005. This I don't want so is there any way to implement this not to include these characters.

Please suggest!
Re: How to identify Unicode Characters [message #412413 is a reply to message #412407] Thu, 09 July 2009 04:00 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I don't think it is very efficient to ask what characters are suppored by SQL Server, and how to use a functionon on SQL Server to figure out which characters are supported in an ORACLE forum.
Re: How to identify Unicode Characters [message #412442 is a reply to message #411882] Thu, 09 July 2009 06:53 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

Hi Thomas

I only want to check those characters which are greater than 7 bit and should be replaced with NULL.
Re: How to identify Unicode Characters [message #412445 is a reply to message #412442] Thu, 09 July 2009 07:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with
  2    data as (select 'aéñàueoüûç' val from dual),
  3    forbidden_chars as (
  4      select substr(replace(sys_connect_by_path(chr(127+level),' '),' ',''),2) chars
  5      from dual
  6      where level = 128
  7      connect by level <= 128
  8    )
  9  select val, translate(val,'a'||chars,'a') new_val
 10  from data, forbidden_chars
 11  /
VAL        NEW_VAL
---------- ----------
aéñàueoüûç aueo

1 row selected.

Regards
Michel
Re: How to identify Unicode Characters [message #412572 is a reply to message #412445] Fri, 10 July 2009 01:01 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

Hi Michel

Thanks for the query!

I tries the query which u have pasted but in my system it return the same value (no change).

PLease refer the attached screen shot.
  • Attachment: screen.JPG
    (Size: 51.62KB, Downloaded 137 times)
Re: How to identify Unicode Characters [message #412587 is a reply to message #412572] Fri, 10 July 2009 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: How to identify Unicode Characters [message #412590 is a reply to message #412587] Fri, 10 July 2009 01:45 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

Even on SQl*PLus it is giving the same output, the one which I was getting earlier.
Re: How to identify Unicode Characters [message #412591 is a reply to message #412590] Fri, 10 July 2009 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post it.

Regards
Michel
Re: How to identify Unicode Characters [message #412595 is a reply to message #411882] Fri, 10 July 2009 01:58 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

This is again I am repeating the matter becasue I didn't get any proper solution on that.

My requirement is to replace the special characters with NULL in oracle table becasue I am migrating this data and this is not accepted by SQL SERVER.
Characters are like

'fi'

'―'

'●'

'η'.

Please let me know a solution.
Re: How to identify Unicode Characters [message #412620 is a reply to message #412595] Fri, 10 July 2009 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I posted one that works.
You refuse to post what you claim.

YOYO (You're On You Own).

Regards
Michel
Re: How to identify Unicode Characters [message #412647 is a reply to message #412620] Fri, 10 July 2009 05:32 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

I didn't refuse to post what I claim?

I posted the same with other words.

Regards
Rajat
Re: How to identify Unicode Characters [message #412648 is a reply to message #412647] Fri, 10 July 2009 05:37 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The problem is, your definition of what you claim are "special characters" changes from post to post.

First you say "everything except ASCII Characters", and then refuse the solution to that.

Then you say "Everything with an ASCII CODE higher than 127", and then refuse the solution to that.

So make up your mind about what "Special Characters" are first.

And when you now say "Any Character not accepted by SQL SERVER", then I alrady told you it is not really effective to ask that Question in an ORACLE forum.

[Updated on: Fri, 10 July 2009 05:48]

Report message to a moderator

Previous Topic: merging 2 databases with all constraints
Next Topic: Regarding Invalid object
Goto Forum:
  


Current Time: Sat Dec 10 12:35:03 CST 2016

Total time taken to generate the page: 0.12122 seconds