March 6, 2007 at 12:35 pm
Greetings, I am new to SQL and have had no training so I hope this does not sound too stupid. I have to add the letter C to the end of each product code in a table and keep getting an error. The product code is the primary key for the table and it is CHAR type. The statement I have been using is this:
UPDATE Database.TableName SET ProductCode = ProductCode + 'C' where Owner = 'OwnerName'
I continually get 'String or Binary Data would be Truncated' error. Can someone please help me out with this. I am stuck!
Thank you!
March 6, 2007 at 12:45 pm
>>I continually get 'String or Binary Data would be Truncated' error
1 or more rows that you are trying to update already have a ProductCode that fills the column. If you need to add 1 more character, you'll need to increase the size of the CHAR datatype.
If it's a primary key, you'll have issues with the foreign keys that reference it and will also have to change their datatypes to make them large enough.
March 7, 2007 at 5:54 am
How did you declare the column. If you used CHAR(x) or NCHAR(x), you've declared a fixed length field and SQL Server pads any entry with spaces. So, if you declared the field as CHAR(3) and then set it to 'A', SQL Server actually sets the column value to 'A '. When you attempt to append the 'C', you're really attempting to set the column to 'A C' which is four characters long (one too long relative to the column declaration).
So, the proper way to do the update when the column is declared char (instead of varchar), is
UPDATE tbl SET col = RTRIM(col) + 'C'
The RTRIM function trims the trailing spaces.
March 7, 2007 at 7:17 am
The previous post is correct. Here's something else to consider:
Add a ProductCodeSuffix column that contains the "C".
OR
Just return the ProductCode with a "C" concatinated at the end. You could use a view.
1 cent worth.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply