Error : String or Binary data would be truncated

Question

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

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

Ariel · Accepted Answer · 2011-09-22 00:20:26Z

1

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.

answered Sep 22, 2011 at 0:20

Ariel

26.8k5 gold badges62 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jojo Over a year ago

Hi @Ariel, thanks for noticing that. but if i use Varchar(1023) it still doesnt work.

jojo Over a year ago

if i use varchar(1023) it doesnt show CCRxSM as CCRx℠ in output.

Ariel Over a year ago

That's because the two have nothing to do with each other. What you need to the character set. You are probably storing the data as ASCII which doesn't have the ℠ character. You need to switch to unicode - UTF-8 probably.

dbooth · Accepted Answer · 2011-09-22 00:21:19Z

1

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

answered Sep 22, 2011 at 0:21

dbooth

1481 gold badge2 silver badges9 bronze badges

Comments

Sparky · Accepted Answer · 2011-09-22 01:21:24Z

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.

Collectives™ on Stack Overflow

Error : String or Binary data would be truncated

3 Answers 3

3 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Related