Msg 8152, Level 16, State 14, Line 1,String or binary data would be truncated.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Msg 8152, Level 16, State 14, Line 1,String...

Msg 8152, Level 16, State 14, Line 1,String or binary data would be truncated.

Rate Topic

Display Mode

Topic Options

Author

Message

Krasavita

Posted Thursday, April 30, 2009 7:03 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, April 19, 2013 8:15 AM
Points: 1,251, Visits: 1,840

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

David-1011936

Posted Thursday, April 30, 2009 7:11 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, February 14, 2013 12:28 PM
Points: 52, Visits: 612

Krasavita (4/30/2009)

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

Krasavita

Posted Thursday, April 30, 2009 7:46 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, April 19, 2013 8:15 AM
Points: 1,251, Visits: 1,840

How would I fix it?

Post #707688

Ramesh Saive

Posted Thursday, April 30, 2009 8:05 AM

SSCrazy

Group: General Forum Members
Last Login: Friday, February 22, 2013 12:03 AM
Points: 2,555, Visits: 2,587

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

tosscrosby

Posted Thursday, April 30, 2009 8:08 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 7:31 AM
Points: 2,832, 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

Ramesh Saive

Posted Thursday, April 30, 2009 8:16 AM

SSCrazy

Group: General Forum Members
Last Login: Friday, February 22, 2013 12:03 AM
Points: 2,555, Visits: 2,587

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 BigGrin

--Ramesh

Post #707710

« Prev Topic | Next Topic »

Permissions