Avoid Junk character in SQL server

Question

Post reply

Avoid Junk character in SQL server

Latheesh NK

SSCertifiable

Points: 6559
More actions
June 29, 2012 at 12:00 am

#260654

Hi All,
I have an excel that I need to port into my SQL server table. But, there are few junk characters as below:
隶草隸草_1
☺☻♥♣♦♠○◘•_1
When am using UNICODE/ASCII , it retuns as 63 which is equivalent to "?". So am really not able to differentiate between these junk charecters and the real value of"?".
Could someone help me out with this how should I proceed?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

subbu1 Hall of Fame Points: 3281 More actions · Answer 1

pls first find special character real values after using below code.

DECLARE @postcode VARCHAR(15)

SET @postcode = 'asd?'

select replace(@postcode,CHAR(63),'')

Gullimeel Hall of Fame Points: 3871 More actions · Answer 2

How are you importing the data? Is the column where you are inserting this data is defined as unicode ( like nvarchar or nvarbinary) ?

Try this.

http://msdn.microsoft.com/en-us/library/ms189941.aspx

You have to use -N flag for bcp to import the unicode data and option DATAFILETYPE = 'widenative' with BULK INSERT.

GulliMeel

http://gullimeelsqlsybase.wordpress.com/2012/06/28/finding-top-n-worst-performaing-queries/

http://gullimeelsqlsybase.wordpress.com/2012/06/26/improved-merge-join-algorithm-in-sql-server-2008/

GulliMeel

Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 3

Gullimeel (6/29/2012)
How are you importing the data? Is the column where you are inserting this data is defined as unicode ( like nvarchar or nvarbinary) ?

Thank you for your help!!!

Importing data through a web service where the info is coming as XML file, later in the sp, using XML handle inserting into a table. The destination datatype is nverchar(100).

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 4

Please find example as below:

Create Table T11(TXT nvarchar(100)

Insert into T11 Select '?? ??_1'

Insert into T11 Select '????????•_1

'

Select * From T11

Select unicode(txt),ascii(txt) From T11

Gullimeel Hall of Fame Points: 3871 More actions · Answer 5

Sorry I do not have access to SQL server as of now. So I can not look into it.

But you can try following Put N before the character you are inserting. This should work.

Create Table T11(TXT nvarchar(100)

Insert into T11 Select N'?? ??_1'

Insert into T11 Select N'????????•_1'

Select * From T11

Select unicode(txt),ascii(txt) From T11

GulliMeel

Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 6

Hi GulliMeel

Thats the trick. You made my life easier. Thank you very much!!!

Thanks,

SQLZealot