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
– Jasen, Commented Dec 27, 2014 at 21:42

Gordon Linoff · Accepted Answer · 2014-12-27 21:36:01Z

4

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

answered Dec 27, 2014 at 21:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related