Post reply

Avoid Junk character in SQL server

  • 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?

  • June 29, 2012 at 12:15 am

    #1506951

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

    DECLARE @postcode VARCHAR(15)

    SET @postcode = 'asd?'

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

  • June 29, 2012 at 12:26 am

    #1506954

    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]

  • June 29, 2012 at 1:03 am

    #1506963

    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).

  • June 29, 2012 at 1:09 am

    #1506965

    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

  • June 29, 2012 at 1:26 am

    #1506968

    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]

  • June 29, 2012 at 3:14 am

    #1507004

    Hi GulliMeel

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

    Thanks,

    SQLZealot

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

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