Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2011
    Posts
    11

    Unanswered: An extra character '?' is getting appended while inserting text string into a table

    Hi,

    I am trying to insert an NTEXT value from one table T1 to another table T2 within a database. Table structures are as below;

    Create table T1 (T1ID INT NOT NUll, SourceColumn Ntext null)

    Create table T2 (T2ID INT NOT NUll, TargetColumn NVARCHAR(MAX) null)

    Every time when long text value is getting inserted into T2.TargetColumn , it is appending with an unwanted character '?' either at the beginning or at the ending of the text string. Same problem happens even when I am trying to update T2.TargetColumn = T1.TextColumn. Because of this, the same column never matches to the source column and gets updated every time even there is no change...

    I am also converting NTEXT column to NVARCHAR(MAX) and replacing CHAR(10) to '' .

    I am using SQL Server 2012. How can I avoid inserting '?' in T2.TargetColumn . Is there any setting which I need to set in the target table?

    Thanks,

    Hmai

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    How did the conversion happen? it could be that the replace of CHAR(10) is causing a problem. Maybe you should switch to replacing NCHAR(10), but that is a guess. This code should start you on being able to tell what the last character in the field is.

    Code:
    drop table #test
    create table #test
    (col1 nvarchar(10),
     col2 ntext)
    
     insert into #test values (replicate (nchar(1041), 10), replicate (nchar(1041), 10))
    
     select col1, col2, unicode (substring (col2, datalength(col2)/2, 1)), unicode (substring (col2, datalength(col2)/2, 1))
     from #test
    You should substitute the temp table with your own table, or a join between your tables as necessary.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •