Written by:
2/1/2008 

The NTEXT field in some companies is overused, but when it is used correctly, it can provide incredible benefits to your applications in many areas.  For instance, in SQL Server 2000, this was the main field for holding HTML and XML due to its flexibility in handling large chunks of text.

The bad part comes when you need to compare or check the value in that field.  Let's take the following example... 

CREATE TABLE [TestTable] ([record_id] INT IDENTITY(1,1), [html_text] NTEXT)

-- populate the table here

SELECT * FROM [TestTable] WHERE [html_text] LIKE '<table id="tblTable1" %'
SELECT * FROM [TestTable] WHERE [html_text] = '<div>placeholder</div>%'

In the previous example, the query will fail because the NTEXT data type does not support comparisons.  More specifically, you would likely be met with an error like this:

The data types ntext and varchar are incompatible in the equal to operator.

Well, in SQL Server 2000, you had a heck of a time dealing with this.  However, if you are lucky enough to be using this data type on an SQL Server 2005 database, you are in luck because there is an easy way to deal with this.  (I should add that hopefully this field is in a legacy table, as you shouldn't be creating new fields using the NTEXT data type anymore.)

SQL Server 2005 introduced the ability to assign a new size to the NVARCHAR data type, MAX.  This in effect allows you to have an unlimited amount of text in a given field, but still maintain the features that the NVARCHAR data type offers.  Well, using a simple CAST call, you can modify your previous query like shown in the next code snippet.

CREATE TABLE [TestTable] ([record_id] INT IDENTITY(1,1), [html_text] NTEXT);

-- populate the table here

SELECT * FROM [TestTable] WHERE CAST([html_text] AS NVARCHAR(MAX)) LIKE '<table id="tblTable1" %';
SELECT * FROM [TestTable] WHERE CAST([html_text] AS NVARCHAR(MAX)) = '<div>placeholder</div>%';

Now, thanks to the conversion of the NTEXT data type to NVARCHAR(MAX), the query will run and return results like you're used to.

Trackback Print

Tags:

Categories: SQL Server

Location: Blogs The Mighty Blog

Your name:
Your email:
	(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Add Comment   Cancel