How to add a row at the end of this column in SQL Server?

Question

I have a project that I am working on. based on a standby SQl Server database from a production server. They have more than 16,000 user email addresses, and I want to damage them, so the system (which has automatic mail) will not send emails to valid addresses.

But I still want users, and I want them in such a way that I can undo what I do (so I don't want to delete them).

SQL I'm trying to do:

UPDATE CONTACT SET
EmailAddress = EmailAddress + '.x'

But it does not work, what am I doing wrong?

The error message is as follows:

--------------------------- Microsoft SQL Server Management Studio Express --------------------------- SQL Execution Error. Executed SQL statement: UPDATE Contact SET EmailAddress = EmailAddress + '.x' Error Source: .Net SqlClient Data Provider Error Message: String or binary data would be truncated. The statement has been terminated. --------------------------- OK Help --------------------------- 

Answer 1

The problem is that EmailAddress +".x" causes some data to be long for this field. You can do:

 select * from Contact where len(EmailAddress +".x") > LENFIELD 

Replace LENFIELD with the length of the column defined in the table. If you just want to poach data, why not just set all the fields to the same email address? Or change the lines that cause the error to be shorter.

Answer 2

Can you clarify what errors you get? I just hit the example and everything works fine.

Edit - the EmailAddress fields that you are trying to update are already close to the full size of the field, to make sure that the editing applies to all the necessary record, you need to change the addition of 2 to the column size for this field

BTW Sql to convert it again

 update Contact set EmailAddress = SUBSTRING(EmailAddress , 0 , len(EmailAddress ) - 1) where SUBSTRING(EmailAddress , len(EmailAddress ) - 1, 2) = '.x' 

Answer 3

Are these fully functional email addresses with @domain.name ? In this case, you can use UPDATE ... SELECT REPLACE to change the @ value to, say, *.

Answer 4

You are looking for the updatetext function: http://msdn.microsoft.com/en-us/library/ms189466.aspx

Answer 5

It seems to me that adding extra text will make one or more email addresses longer than the size of the field. Instead of adding, why don't you replace the last character with another?

Answer 6

The first Google search result in the error message:

"String or binary data will be truncated" MS Sql error

This problem occurs when you try to insert a string into the field that exceeds the length of the field. The only solution I could find was to set a large field length.

Link: http://www.dotnetjunkies.com/WebLog/skiff/archive/2005/01/31/49336.aspx

Answer 7

to try:

UPDATE Contact SET EmailAddress = EmailAddress || '.x';

|| is a string concatenation operator (varchar) in SQL.

TIP. Error messages will help if you ask more questions.