SQL Server : replace script error gives me String or binary data would be truncated

Question

I am self taught with SQL Server. I been using the replace script lately. I have to replace a string inside of a varchar column.

An example of what I need to change is 15151500001500000000 where I need to change the last 00 to 10

This is the script I am using:

UPDATE xxxxx
   SET craftname = REPLACE(craftname, 00, 10)
 WHERE craftname like '%00'

However it gives me this error every time

String or binary data would be truncated.

I searched around the net and from what I can see most common reason is that the column is getting to big but here I am replacing 2 digits with 2 digits so that shouldn't happen.

Any ideas?

really? `REPLACE(craftname, 00,10)` SQL server allows that? I'm guessing it converts the integers to strings and then uses them in the replace operation. so you get `REPLACE(craftname, '0','10')` which is probably not what you want. — Jasen, Dec 27 '14 at 21:42

score 4 · Accepted Answer · answered Dec 27 '14 at 21:36

Try using strings instead of integers:

UPDATE xxxxx
   SET craftname = REPLACE(craftname, '00', '10')
 WHERE craftname like '%00';

The problem with your version is that every 0 is replaced by 10, which increases the length of the string. The integers are turned into strings using "reasonable" representations, so 00 becomes '0', rather than '00'.

The above still won't do what you want, because it will replace every occurrence of 00 with 10. I included it to show you how to fix the run-time error.

If you just want to change the last two characters, don't use REPLACE(). Instead:

UPDATE xxxxx
   SET craftname = LEFT(craftname, len(craftname) - 2) + '10'
 WHERE craftname like '%00';

SQL Server : replace script error gives me String or binary data would be truncated

1 Answers1