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   
---------------------------

One thing you're doing wrong is not saying what "not working" means! — John Saunders
– John Saunders, Commented Mar 16, 2009 at 1:56

Soner Gönül · Accepted Answer · 2013-03-27 09:07:41Z

7

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.

edited Mar 27, 2013 at 9:07

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

answered Mar 16, 2009 at 2:06

JoshBerke

67.3k25 gold badges131 silver badges170 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ash Over a year ago

I am pretty sure that its not - varchar(150) - but I will check.

Ash Over a year ago

Ok, so one record is returned when i execute that code, but it must have added whitespace becasue its not that long. I will try and trim.

Ash Over a year ago

Ok so the solution was pretty simple: UPDATE Contact SET EmailAddress = RTRIM(EmailAddress) + '.x'

MrTelly · Accepted Answer · 2009-03-16 02:04:02Z

6

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'

edited Mar 16, 2009 at 2:04

answered Mar 16, 2009 at 1:58

MrTelly

14.9k2 gold badges52 silver badges84 bronze badges

Comments

Dan Breslau · Accepted Answer · 2009-03-16 02:45:33Z

3

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

answered Mar 16, 2009 at 2:45

Dan Breslau

11.5k2 gold badges37 silver badges44 bronze badges

Comments

Aidan Ryan · Accepted Answer · 2009-03-16 02:06:15Z

1

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?

answered Mar 16, 2009 at 2:06

Aidan Ryan

11.7k14 gold badges60 silver badges87 bronze badges

Comments

Martlark · Accepted Answer · 2009-03-16 01:58:10Z

-2

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.

answered Mar 16, 2009 at 1:58

Martlark

14.6k14 gold badges86 silver badges103 bronze badges

Comments

vmarquez · Accepted Answer · 2009-03-16 02:06:08Z

-2

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

answered Mar 16, 2009 at 2:06

vmarquez

1,36711 silver badges21 bronze badges

2 Comments

Aidan Ryan Over a year ago

Plenty of ways to get the desired result of invalid email address that don't require increasing the field length.

vmarquez Over a year ago

You are right Aidan but OP's question was: "But it isnt working, what am I doing wrong?"

Collectives™ on Stack Overflow

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

6 Answers 6

3 Comments

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

3 Comments

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related