April 30, 2009 at 7:03 am
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 '%-')
April 30, 2009 at 7:11 am
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.
April 30, 2009 at 7:46 am
How would I fix it?
April 30, 2009 at 8:05 am
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
April 30, 2009 at 8:08 am
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.
April 30, 2009 at 8:16 am
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