Null fields causing problems - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Null fields causing problems

Author

Topic

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-12-30 : 11:43:49

I am trying to do this:


SET CONCAT_NULL_YIELDS_NULL OFF
UPDATE tblAccurint_Above1500
SET akas_ssn_2 = SUBSTRING(akas_ssn_2,0,4)+'-'+SUBSTRING(akas_ssn_2,4,2)+'-'+SUBSTRING(akas_ssn_2,6,4)
WHERE akas_ssn_2 IS NOT NULL

It gives me this error:

String or binary data would be truncated.
The statement has been terminated.

Some of the fields are blank. How do I get around this? Thanks!

Brenda

If it weren't for you guys, where would I be?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-12-30 : 11:49:35

I don't think it's to do with the blank fields....more to do with the field now exceeding it's designed width...what's the DDL for the "tblAccurint_Above1500" table?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-30 : 11:51:10

this has nothing to do with nuls. and even if it had
WHERE akas_ssn_2 IS NOT NULL
renders SET CONCAT_NULL_YIELDS_NULL OFF unnecessary.
the problem is you're trying to update your columns with a longer string that the column will hold.

Go with the flow & have fun! Else fight the flow