Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

sql-server

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   
---------------------------
like image 750
Ash Avatar asked Mar 16 '09 01:03

Ash


1 Answers

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.

like image 167
JoshBerke Avatar answered Sep 28 '22 09:09

JoshBerke