Error : String or Binary data would be truncated

i have written a query as below. lets say i have data in @Test table as "New York and Texas have CCRxSM federraly approved". Word CCRxSM is a superscript word but it didnt loaded as superscript. SM should be on top like CCRx℠.

Declare @Test Table (Foo1 varchar(1023))
Insert @Test(Foo1) Values ('New York and T开发者_JS百科exas have CCRxSM federraly approved');
Select Foo1, 
 Case When Right(Foo1, 21) = 'SM'
  Then Left(Foo1, Len(Foo1) - 2) + NChar(8480) 
  Else Foo1 End
From @Test

when i run my query i get error String or Binary data would be truncated. I am using SQL SERVER 2005.

JUST WANTED TO ADD, IF I JUST USE WORD CCRxSM THEN IT WORKS. so dont know why it doesnt work for complete record.

Thanks

You defined your column Foo1 to have a max length of 10, then you tried inserting 51 characters in it. So obviously it would truncate.

And CCRxSM doesn't look superscript to me. It's just letters. Superscript and things like that are formatting.

The Foo1 column in the @Test table can only be 10 characters long, so when you run your insert statement, it would only insert 'New York a'. The rest will be truncated.

You can change it to

DECLARE @Test TABLE (Foo1 varchar(100))...

Try this:

Declare @Test Table (Foo1 nvarchar(1023))
Insert @Test(Foo1) Values ('New York and Texas have CCRxSM federraly approved');
Select Foo1, REPLACE(foo1,'xSM','x'+nchar(8480)) as updatedFoo1
From @Test

Your query

  Declare @Test Table (Foo1 varchar(1023))
    Insert @Test(Foo1) Values ('New York and Texas have CCRxSM federraly approved');
    Select Foo1, 
     Case When Right(Foo1, 21) = 'SM'
      Then Left(Foo1, Len(Foo1) - 2) + NChar(8480) 
      Else Foo1 End
    From @Test

Based on the code, the right(foo1,21) is SM federraly approved not just SM Also, keep in mind if you run this in query analyzer, be sure the query results are in a Unicode font so you can see the SM superscript.