CODEWITHSUNDEEP
sql
KennyH
KennyH

Reputation: 773

SQL Update and Replace

Can someone guide me in the right direction. I'm trying do an update and replace on a column in a database. This is the SQL code that I am using (SQL 2008 Express Edition).

*update GameList set image = replace(Image, 'NintendoDS', 'Nintendo DS') WHERE Image LIKE '%NintendoDS%'*

Image
/webimages/NintendoDS/boxmonstersvsaliens.jpg

This is the error I am getting:
String or binary data would be truncated. The statement has been terminated.

Upvotes: 1

Views: 974

Answers (2)

RichO
RichO

Reputation: 733

Two things come to mind. First is that your column is not long enough; however I suspect that's not the case and this will be fixed by using:

update GameList set image = rtrim(replace(Image, 'NintendoDS', 'Nintendo DS')) 
WHERE Image LIKE '%NintendoDS%'

If your column is padded with trailing blanks, then the original replace will fail because adding the extra space exceeds the maximum column length. R-Trimming the result prevents this problem.

Upvotes: 3

Hogan
Hogan

Reputation: 70523

The problem is there is a row where adding the extra space (or spaces if the test string is repeated) would make the resulting string bigger than the column size.

For example if the column is declared varchar(20) you are trying to add a string of size 21

Upvotes: 5

Related Questions