SELECT values from ntext column are truncated

Question

I have table which consists column of ntext data type.

I write query that search for some example, like:

SELECT 
    Id,
    NtextColumnName
FROM Table
WHERE 
    NtextColumnName LIKE N'%search_term%'

and got certain number of rows. but, when viewing this column to find this search_term I don't see it, it seems that result is truncated.

I also tried to show result in text and apply Find, but no result.

How can I examine existence of some search term in NtextColumnName column, and also make this result visible?

I got output with truncated result. and when export result in notepad I can't even find `search_term` in that result — veljasije, Apr 09 '13 at 13:31
The gridview in SSMS has limitation i think to the length of the record. Try to set the result to file and then check what is in the file. — dferidarov, Apr 09 '13 at 13:31
I tried but same problem. Still I can't find `search_term` from query in my result, though I got 30 rows for that search criteria — veljasije, Apr 09 '13 at 13:34
I seem to remember (though can't find a link yet) that even the "to file" output truncates columns. — Damien_The_Unbeliever, Apr 09 '13 at 13:36
[Article to read](http://stackoverflow.com/a/564811/2232256) — dferidarov, Apr 09 '13 at 14:00

score 2 · Answer 1 · edited May 23 '17 at 12:13

Depending on how long your column data is, if you're just looking to view the data in Management Studio, cast the column to an XML data type:

declare @t1 table(c1 ntext)
insert into @t1 values(space(5000) + 'a' + space(5000))
select cast(c1 as xml) from @t1

That example will give you a single column, which you can click on to see the entire set of data in a new window.

The settings for maximum amount of data returned:

Tools | Options | Query Results | SQL Server | Results to Text | Maximum number of characters displayed in each column
Tools | Options | Query Results | SQL Server | Results to Grid | Maximum Characters Retrieved

Edit: to get the full value, for larger data sizes, this answer points to the correct way to wrap it in a CDATA column:

declare @t1 table(c1 ntext)
insert into @t1 values(space(10000) + 'a' + space(10000))
select 
  convert(xml,'<xml><![CDATA[' + cast(c1 as varchar(max)) + ']]></xml>') 
from 
  @t1

SELECT values from ntext column are truncated

1 Answers1

Linked