How do I compare a value to an ntext column?

Question

I am working on a project in which i am saving pdf 's text extracted by itextsharp in a column of sql server database.For some requirements i have to search document by giving document text as a parameter to a procedure to find the document.My code is

   @docText varchar(8000)

   select * from document d

   where convert(varchar(8000),d.text) = @docText

Problem is that when document text size is less then 8000 (for example 2600 )then it is not matched by this query and in above query we can not set size as convert(varchar(len(@docText)),d.text).

How to set column (text) size equal to parameter size dynamically passed to the procedure.

possible duplicate of [NTEXT comparison](http://stackoverflow.com/questions/3543570/ntext-comparison). As the [documentation says](http://stackoverflow.com/questions/3543570/ntext-comparison) you can't use `=` but you [can use `LIKE`](http://msdn.microsoft.com/en-us/library/ms187993(v=sql.100).aspx) although please note the statement on the second page that says that `ntext` should not be used any more. — Pondlife, Apr 02 '13 at 21:15

score 1 · Accepted Answer · answered Apr 02 '13 at 02:26

My first thought is that you need this:

declare @docText varchar(max);

select *
  from document d
 where d.text = @docText;

VARCHAR(MAX) is a type that will hold any varchar from size 0 bytes to 2 GB. It's called VARying-CHARacter because of its variable nature. The trailing spaces are automatically considered insignificant, i.e. ABCDEF_____ = ABCDEF (the first has 5 trailing spaces).

For this reason (trailing space handling), there is no need for CONVERT(varchar(<exact-length>), text).

What type is the column d.text defined as? If it is VARCHAR(8000), then I would suggest upgrading it to VARCHAR(MAX). A book surely has more than 8000 characters?

Are you sure you're not padding the input @docText with characters to make up 8000, e.g. adding trailing dots or byte(0) characters?

type of d.text is ntext and i have removed empty spaces from the text before saving and reading it.I have tried your suggestion but it does not show result. — mck, Apr 02 '13 at 03:40

score 1 · Answer 2 · answered Apr 02 '13 at 02:36

1

Try this

where Charindex(d.text, @docText) > 0

answered Apr 02 '13 at 02:36

wy__

301
1
5

not working because type of d.text is ntext. – mck Apr 02 '13 at 03:58
Have you tried convert d.text to varchar? `where Charindex(convert(varchar(max),d.text), @docText) > 0` – wy__ Apr 02 '13 at 04:23
yes i tried but still no result – mck Apr 02 '13 at 04:34

score 1 · Answer 3 · answered Apr 02 '13 at 21:51

try:

declare @docText varchar(max)
set @doctext = 'test'
select * from document d
where convert(varchar(max),d.text) like '%' +@docText+ '%'

This should convert the text values to something that can readily use comparisons and allow you to serach for a specific text block anywhere in the document.

You also should consider if you want to use a fulltext search.

Next thing is that you really need to change the text field to varchar (max) as text is deprecated. This is a critical change that needs to be made to your database. There is no reason any 2008 database should still be using text or ntext data types. When you don't make changes for deprecated items, you cause the path to upgrade to be much more painful than it should be when the feature is removed (typiocally it takes several database upgrades before deprecated items are removed)and will eventually mean that you cannot upgrade. This type of database maintenance needs to be done and tested long before you want to upgrade.

How do I compare a value to an ntext column?

3 Answers3