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

  • 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 '%-')

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

  • How would I fix it?

  • 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


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

  • I missed mentioning that the query can also be written by excluding the FROM clause

    UPDATEdbo.tblCust

    SETLAST_NAME = SUBSTRING( FIRST_NAME, CHARINDEX( ' ', FIRST_NAME ) + 1, 100 ) + LAST_NAME,

    FIRST_NAME = SUBSTRING( FIRST_NAME, 1, CHARINDEX( ' ', FIRST_NAME + ' ' ) - 1 )

    WHEREFIRST_NAME LIKE '%-'

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

    --Ramesh


Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply