How to Append String at the end of a Given a Column in SQL Server?

Question

I have a project I am working on. based off a backup SQl Server database from a production server. They have over 16,000 user email addresses, and I want to corrupt them so the system (which has automatic emailers) will not send any emails to valid addresses.

But I still want the users, and I want them in a way that I can reverse what I do (which is why I dont want to delete them).

The SQL I am trying is:

UPDATE Contact SET
EmailAddress = EmailAddress + '.x'

But it isnt working, what am I doing wrong?

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

try:

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

the || is the string (varchar) concatanation operator in SQL.

HINT: Error messages would help if asking more questions.

Answer 2

Can you be more specific about any errors that you get? I've just knocked up an example and it works fine.

Edit - EmailAddress fields you're trying to update are already close to the full size for the field, to make sure the edit applies to all the required record, you need to change add 2 to the column size for that field

BTW Sql to convert it back again

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

Answer 3

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

Answer 4

First result on Google searching for the error message says:

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

This problem occurs when you trying to insert to field a string that exceeds fields length. The only solution I could find was to set a bigger field length.

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

Answer 5

The issue is that EmailAddress +".x" results in some of your data being to long for the given field. You could do:

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

Replace LENFIELD with the length of the column defined on the table. If you just want to mung the data why not just set all the fields to a single email address? Or modify the rows that are causing the error to occur to be shorter.

Answer 6

It looks to me like appending the extra text will make one or more of the email addresses longer than the field size. Rather than appending why don't you replace the last character with a different one?

Answer 7

Are these fully-qualified email addresses, with @domain.name ? In that case, you could use UPDATE... SELECT REPLACE to change the @ to, say, *.