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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy