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.
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 (https://blog.sqlauthority.com)
12 Comments. Leave new
Sir, there is another way to have the answer in varchar itself.. here we go..
SELECT ID, MyText
FROM TestTable
WHERE cast(mytext AS varchar(max))= ‘AnyText’
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 , .
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 :)
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.
I think the “LIKE” one is the simplest, and it worked for me, so…. THANKS!!!
LIKE operator c’not compare strings for equality:
‘111’ like ‘1%1’
This was very helpful, thank you. In fact, whenever I google a SQL Server question and something written by you comes up in the hits, I am happy because I know I’m going to get a well thought-out and easy to understand explanation. Thanks so much for posting helpful information!
Thank you so much you are very kind!
Late to the show, but I just came to this post when planning migration from 100 to 140 / 2008 R2 to 2017:
Just be aware of this, when running this kind of statement:
ALTER TABLE TestTable
ALTER COLUMN MyText NVARCHAR(MAX)
GO
=> Under the hood it’s doing:
UPDATE TestTable
SET MyText = MyText
That might take a while ;-)
I am not sure if this is the right place to ask this but I could not find a questions section.
I am importing an SP from SQL 2008 into Excel 2016 and getting the error: “Error converting data type nvarchar to date.” Here is the script: PS. The script runs fine in SQL.
USE [LA_VOTER]
GO
/****** Object: StoredProcedure [Temp].[dmv_import] Script Date: 6/11/2018 8:26:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: Arash B
— Create date: 6/5/2018
— Description: test for creating dynamic ‘Data Entry Signature Verification’ worksheeet
— =============================================
ALTER PROCEDURE [Temp].[dmv_import]
— Add the parameters for the stored procedure here
@date date = null
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
declare @date_work date
if @date is null
set @date_work = getdate()
else
Set @date_work = @date
/*count of processed*/
SELECT vi.import_code as ‘DMV Category’
,sum(case when convert(date,vi.createdate,110) = @date_work then 1 else 0 end) as ‘received’
,sum(case when convert(date,vi.processed_date,110) = @date_work then 1 else 0 end) as ‘processed’
FROM DIMSNet.dbo.voter_import vi
WHERE
vi.import_type in (‘DMV’)
and import_code < 13
group by import_code
END
Useful
Thanks, it helped –> WHERE CONVERT(NVARCHAR(MAX), MyText) = N’AnyText’