Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Msg 8152, Level 16, State 14, Line 1,String or binary data would be truncated. Expand / Collapse
Author
Message
Posted Thursday, April 30, 2009 7:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 11:58 AM
Points: 1,279, Visits: 1,886
When I ran this statment I get this error:
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated. What is wrong with this statment?


UPDATE TblCust
SET
tblCust.LAST_NAME =
SUBSTRING(FIRST_NAME, CHARINDEX(' ', FIRST_NAME) + 1, 100) + LAST_NAME,

tblCust.first_NAME =
SUBSTRING(FIRST_NAME, 1, CHARINDEX(' ', FIRST_NAME + ' ') - 1)

FROM dbo.TblCust
WHERE (FIRST_NAME LIKE '%-')
Post #707641
Posted Thursday, April 30, 2009 7:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 14, 2013 12:28 PM
Points: 52, Visits: 612
Krasavita (4/30/2009)
When I ran this statment I get this error:
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated. What is wrong with this statment?


UPDATE TblCust
SET
tblCust.LAST_NAME =
SUBSTRING(FIRST_NAME, CHARINDEX(' ', FIRST_NAME) + 1, 100) + LAST_NAME,

tblCust.first_NAME =
SUBSTRING(FIRST_NAME, 1, CHARINDEX(' ', FIRST_NAME + ' ') - 1)

FROM dbo.TblCust
WHERE (FIRST_NAME LIKE '%-')


It looks like the LAST_NAME field is getting appended each time, which is leading to the value becoming too long for that field; that's probably the source of the problem.
Post #707649
Posted Thursday, April 30, 2009 7:46 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 11:58 AM
Points: 1,279, Visits: 1,886
How would I fix it?
Post #707688
Posted Thursday, April 30, 2009 8:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:32 AM
Points: 2,551, Visits: 2,593
Are you sure that the below assignment is correct?
SET tblCust.LAST_NAME = SUBSTRING(FIRST_NAME, CHARINDEX(' ', FIRST_NAME) + 1, 100) + LAST_NAME

You are appending the LAST_NAME value to the extracted substring which is becoming too long to store in the storage space allocated for the column LAST_NAME.

You can either increase the size of the column LAST_NAME to an appropriate value or truncate the long text to the appropriate length text.




--Ramesh

Post #707700
Posted Thursday, April 30, 2009 8:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 7:31 AM
Points: 2,666, Visits: 5,316
Either increase the size of the fileds on the table or shorten the amount of data to fit your current size. One question - why are you adding first name to a field called "tblCust.LastName"?

--Edit: Sorry, Ramesh responded while I was typing but expressed similar concerns!!


-- You can't be late until you show up.
Post #707702
Posted Thursday, April 30, 2009 8:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:32 AM
Points: 2,551, Visits: 2,593
I missed mentioning that the query can also be written by excluding the FROM clause

UPDATE	dbo.tblCust
SET LAST_NAME = SUBSTRING( FIRST_NAME, CHARINDEX( ' ', FIRST_NAME ) + 1, 100 ) + LAST_NAME,
FIRST_NAME = SUBSTRING( FIRST_NAME, 1, CHARINDEX( ' ', FIRST_NAME + ' ' ) - 1 )
WHERE FIRST_NAME LIKE '%-'


Terry, Sometimes I hit the keyboard faster than usual when I spill my coffee over it


--Ramesh

Post #707710
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse