SQL Server text column question
-
Hi - I just have a general SQL server question I'm hoping someone can help answer. I have a table with a column of datatype TEXT called NOTE. I am trying to trim those records in the text field to say, 10000 characters where datalength(NOTE) > 10000. for example, normally i would do something like 'update notetable set note = LEFT(note, 10000) where length(note) > 10000', but of course i can't do this with this datatype. This datatype is a pain..Does anyone know the syntax for this? Thanks!
Marcus Spitzmiller You will never hear me say, "I listen to pretty much everything." That is for people who don't care what goes in their ears.
-
Hi - I just have a general SQL server question I'm hoping someone can help answer. I have a table with a column of datatype TEXT called NOTE. I am trying to trim those records in the text field to say, 10000 characters where datalength(NOTE) > 10000. for example, normally i would do something like 'update notetable set note = LEFT(note, 10000) where length(note) > 10000', but of course i can't do this with this datatype. This datatype is a pain..Does anyone know the syntax for this? Thanks!
Marcus Spitzmiller You will never hear me say, "I listen to pretty much everything." That is for people who don't care what goes in their ears.
Convert to/from VARCHAR?
-
Convert to/from VARCHAR?
No - it'll still ultimately reside in the same column - I just need to trim it. any thoughts?
Marcus Spitzmiller You will never hear me say, "I listen to pretty much everything." That is for people who don't care what goes in their ears.
-
No - it'll still ultimately reside in the same column - I just need to trim it. any thoughts?
Marcus Spitzmiller You will never hear me say, "I listen to pretty much everything." That is for people who don't care what goes in their ears.
I don't see why what PIEBALDconsult suggested will not work:
select cast(mytextcolumn as varchar(1000)) as MyTextColumnTrimmed from mytesttable
Try it out.
--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
-
I don't see why what PIEBALDconsult suggested will not work:
select cast(mytextcolumn as varchar(1000)) as MyTextColumnTrimmed from mytesttable
Try it out.
--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
I guess I misunderstood the suggestion. However, I did: select cast(NOTE as varchar(16349)) as NOTE from S_NOTE_ACCNT WHERE datalength(NOTE) > 16349 and I get: The size (16349) given to the type 'varchar' exceeds the maximum allowed for any data type (8000). So really, I need to trim the field to 16349, but apparently I can't this way...any thoughts?
Marcus Spitzmiller You will never hear me say, "I listen to pretty much everything." That is for people who don't care what goes in their ears.
-
Hi - I just have a general SQL server question I'm hoping someone can help answer. I have a table with a column of datatype TEXT called NOTE. I am trying to trim those records in the text field to say, 10000 characters where datalength(NOTE) > 10000. for example, normally i would do something like 'update notetable set note = LEFT(note, 10000) where length(note) > 10000', but of course i can't do this with this datatype. This datatype is a pain..Does anyone know the syntax for this? Thanks!
Marcus Spitzmiller You will never hear me say, "I listen to pretty much everything." That is for people who don't care what goes in their ears.
I don't have the data to test with but, I do believe that SUBSTRING works with the TEXT & NTEXT data types.
UPDATE noteTable SET note = SUBSTRING(note,0,10000) WHERE DATALENGTH(note) > 10000 -
I don't have the data to test with but, I do believe that SUBSTRING works with the TEXT & NTEXT data types.
UPDATE noteTable SET note = SUBSTRING(note,0,10000) WHERE DATALENGTH(note) > 10000Thanks, that works. - Apparently I didn't look hard enough. I guess I didn't realize that substring existed. I tried substr and when that didn't work, I guess I thought that that function didn't exist. Thanks again!
Marcus Spitzmiller You will never hear me say, "I listen to pretty much everything." That is for people who don't care what goes in their ears.