<div>
The first line does not include the table name, how will it know from where ? <br />
<br />
When I ran the update I get this error:<br />
<br />
Msg 102, Level 15, State 1, Line 3<br />
Incorrect syntax near '&gt;'.<br />
<br />
this is what I ran:<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-41420392-1"><span>Update Employment set JobDesc=substring(JobDesc,0,8000) where len(JobDesc &gt; 8000)</span>
</code></pre>
</div>


<div>
When running the truncate<br />
<br />
Msg 8116, Level 16, State 1, Line 1<br />
Argument data type text is invalid for argument 1 of len function.
</div>


<div>
<blockquote>
Msg 8116, Level 16, State 1, Line 1<br />
Argument data type text is invalid for argument 1 of len function.
</blockquote>
<br />
You cannot have string function in columns with text data type, string functions are only supported for varchar columns.
</div>


<div>
OK, lets back up a little. I want to truncate a text column to 8000 characters long.
</div>


<div>
The code which I shared will help you, did you tried that?
</div>


<div>
I ran: <br />
<br />
SELECT ID,datalength(JobDesc), CONVERT(VARCHAR(MAX), JobDesc)<br />
FROM Employment<br />
WHERE datalength(JobDesc) &gt;&nbsp;8000<br />
<br />
Fortunately I only get 2 results out of hundreds of records. I don't want to create a temporary table, which statement do I run to truncate the 2 results from the above table/column ? <br />
<br />
I can't run this: &nbsp; ALTER TABLE Employment ALTER COLUMN JobDesc VARCHAR(8000)<br />
<br />
because it says data would be truncated. I need to trim those two records to 8000 characters
</div>


<div>
Correction.<br />
<br />

<pre><code class="code-10 nolinks" id="code-20-41420528-1"><span>DECLARE @ptr BINARY(16), @MyTextData VARCHAR(MAX)</span>
<span>DECLARE C CURSOR FOR </span>
<span>SELECT TEXTPTR(C1), C1</span>
<span>FROM TextTable</span>
<span>WHERE datalength(C1) &gt; 8000</span>
<span></span>
<span>OPEN C</span>
<span>FETCH NEXT FROM C INTO @Ptr, @MyTextData</span>
<span>WHILE @@FETCH_STATUS = 0</span>
<span>BEGIN</span>
<span>		SELECT @MyTextData = LEFT(@MyTextData,8000)</span>
<span></span>
<span>		WRITETEXT TextTable.C1 @ptr  @MyTextData</span>
<span></span>
<span>		FETCH NEXT FROM C INTO @Ptr, @MyTextData</span>
<span>END	</span>
<span></span>
<span>CLOSE C</span>
<span>DEALLOCATE C</span>
<span></span>
<span>GO</span>
</code></pre>
</div>


<div>
For the last SQL I get this error:<br />
<br />
Msg 8116, Level 16, State 1, Line 4<br />
Argument data type text is invalid for argument 1 of len function.<br />
<br />
I decided to award the points but none of them have worked so far. Since its only 2 records causing the issue I will delete them manually for this one client and then Ill be able to run the rest of my queries. Thank you!
</div>


<div>
Did my <br />
Update tablename set column=substring(column,0,<wbr />8000) where len(column)&gt;8000<br />
Not do what you wanted?
</div>


<div>
Nop. I get this error:<br />
<br />
Msg 8116, Level 16, State 1, Line 1<br />
Argument data type text is invalid for argument 1 of len function.<br />
<br />
I kept getting all sort of errors so I just deleted the two entries manually.
</div>


<div>
Turns out I ran this and it worked, much simpler:<br />
<br />
&nbsp;DELETE FROM dbo.Employment<br />
&nbsp; WHERE datalength(JobDesc) &gt;&nbsp;8000
</div>


<div>
Depending your DB design, you may now have orphan records that referred to these records........<br />
If it is ok for you, ...
</div>


<div>
<div class="content wysiwyg-content">
I am changing a field from text field to varchar(8000), there are one or two records with more than 8000 characters.<br />
How can I truncate them so that I can run the conversion ? <br />
<br />
the table name is: employment<br />
the field is: jobdesc
</div>
</div>


I am changing a field from text field to varchar(8000), there are one or two records with more than 8000 characters.
How can I truncate them so that I can run the conversion ? 

the table name is: employment
the field is: jobdesc

truncate field to 8000 characters

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.