add to the end of a field's data

[barbola]

I have a description field and want to add to the descriptions, but they are all different.

Acct Old Description New Description
1001 Sales Sales - Division1
2001 Sales Sales - Division2
1002 COGS COGS - Division1

etc.

Here is how I tried it:

update tblAccount set actdescr = actdescr+'- Division1' where left(acct='100')

I am getting this error:

Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

and I think it's because there are trailing spaces in the current account description, but not sure how to get around this?

Thanks and Happy New Year!

 

[hamking11]

Trim(actdescr)

 

[mrdenny]

The field "actdescr" is smaller than the data which you are putting into the field.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[barbola]

Denny, the field is like 64 chars or something like that.

I tried Trim, and it said that Trim was not recognized.

 

[RobertT687]

Try:
update tblAccount set actdescr = RTrim(actdescr) +' - Division1' where left(acct='100')

RTrim and LTrim are the TSQL functions to trim spaces from the right and left side of a string.

 

[mrdenny]

Is it possible that the resulting field values will be over 64 characters (the correct answer here is yes).

Try this query to identify the offending rows.

select *
from tblAccount 
where len(actdescr+'- Division1') > 64
and left(acct='100')

This should show you all the records which are causing the warning.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[bboffin]

The Left function looks very odd.

Shouldn't it be Left(acct, 3) = '100' ?

This will only work if acct is a string type field.

If acct is an int then it would need to be

Left(cast(acct as varchar(10)), 3) = '100'

Also the solution proposed doesn't solve the problem for Division 2 lines.

Assuming that acct is an int and that the first character of acct indicates the division then a more general solution might be:

Update tblAccount Set actdescr = rtrim(actdescr) + ' - Division ' + left(cast(acct as varchar(10), 1)

Bob Boffin

 

[barbola]

Actually that was just an example. Our account number is split into four separate fields (not int). I will be doing each division separately.

I will try this:

select * from tblAccount where len(actdescr+'- Division1') > 64 and left(acct='100')

Then I think this will work:

update tblAccount set actdescr = RTrim(actdescr) +' - Division1' where left(acct='100')

thanks