SQL SERVER – Fix: Error : 402 The data types ntext and varchar are incompatible in the equal to operator

Some errors are very simple to understand but the solution of the same is not easy to figure out. Here is one of the similar errors where it clearly suggests where the problem is but does not tell what is the solution. Additionally, there are multiple solutions so developers often get confused with which one is correct and which one is not correct.

Let us first recreate scenario and understand where the problem is. Let us run following

USE Tempdb
GO
CREATE TABLE TestTable (ID INT, MyText NTEXT)
GO
SELECT ID, MyText
FROM TestTable
WHERE MyText = 'AnyText'
GO
DROP TABLE TestTable
GO

When you run above script it will give you following error.

Msg 402, Level 16, State 1, Line 1
The data types ntext and varchar are incompatible in the equal to operator.

One of the questions I often receive is that voucher is for sure compatible to equal to operator, then why does this error show up. Well, the answer is much simpler I think we have not understood the error message properly. Please see the image below. The next and varchar are not compatible when compared with each other using equal sign.

http://www.pinaldave.com/bimg/402error.jpg

Now let us change the data type on the right side of the string to nvarchar from varchar. To do that we will put N’ before the string.

USE Tempdb
GO
CREATE TABLE TestTable (ID INT, MyText NTEXT)
GO
SELECT ID, MyText
FROM TestTable
WHERE MyText = N'AnyText'
GO
DROP TABLE TestTable
GO

When you run above script it will give following error.

Msg 402, Level 16, State 1, Line 1
The data types ntext and nvarchar are incompatible in the equal to operator.

You can see that error message also suggests that now we are comparing next to nvarchar. Now as we have understood the error properly, let us see various solutions to the above problem.

Solution 1: Convert the data types to match with each other using CONVERT function.

Change the datatype of the MyText to nvarchar.

SELECT ID, MyText
FROM TestTable
WHERE CONVERT(NVARCHAR(MAX), MyText) = N'AnyText'
GO

Solution 2: Convert the data type of columns from NTEXT to NVARCHAR(MAX) (TEXT to VARCHAR(MAX)

ALTER TABLE TestTable
ALTER COLUMN MyText NVARCHAR(MAX)
GO

Now you can run the original query again and it will work fine.

Solution 3: Using LIKE command instead of Equal to command.

SELECT ID, MyText
FROM TestTable
WHERE MyText LIKE 'AnyText'
GO

Well, any of the three of the solutions will work. Here is my suggestion if you can change the column data type from ntext or text to nvarchar or varchar, you should follow that path as text and ntext datatypes are marked as deprecated. All developers any way to change the deprecated data types in future, it will be a good idea to change them right early.

If due to any reason you can not convert the original column use Solution 1 for temporary fix. Solution 3 is the not the best solution and use it as a last option. Did I miss any other method? If yes, please let me know and I will add the solution to original blog post with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

4 thoughts on “SQL SERVER – Fix: Error : 402 The data types ntext and varchar are incompatible in the equal to operator

  1. I have found three solution, it may work .

    SELECT ID, MyText
    FROM TestTable
    WHERE SUBSTRING(MyText,0,DATALENGTH(MyText))=N’AnyText’

    SELECT ID, MyText
    FROM TestTable
    WHERE PATINDEX(N’AnyText’, MyText) =1

    SELECT ID, MyText
    FROM TestTable
    WHERE CHARINDEX(N’AnyText’, MyText) =1 AND DATALENGTH(N’AnyText’)=DATALENGTH(MyText)

    These solutions may not work in some criteria : like leading spaces, trailing spaces, etc…

    However, As mentioned by you , solution 2 will be most preferable. ntext type will be removed in future versions (http://msdn.microsoft.com/en-us/library/ms187993.aspx), .

    Like

  2. This article really helped me, thank you very much!
    Had the same issue, trying to compare a varchar with a text field. Im using LIKE instead of “=” operator, its working fine now :)

    Like

  3. Mr, Harsh .Unfortunately I can’t agree with you. How would you know that the value N’AnyText’.A column may have millions of unknown record.if you don’t have the chance to know in advance the row field then how this process would work.We can also say select * from infomation_schema.Columns where table_name like ‘%mytable%’ and find out the ntext.This is not viable. If you can test without knowing the row value then its a good solution.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s