CODEWITHSUNDEEP
sqlsql-servert-sql
Dartfrog Imi
Dartfrog Imi

Reputation: 529

SQL Query Finding From Table DataType Declaration

I got some serial keys to find in sql database, such as “A-B-C”,”D-E-F”,”G-H-I”,”J-K-L” and they are stored in tblTemp using ntext data type. These above keys may store in three columns, colA, colB and colC (sometimes store in one column and the rest are null). Sometimes, two serial keys can find in one column (e.g. A-B-C;D-E-F) using “;” seperated. so i wrote the following sql query.

Declare @sa TABLE(var1 nvarchar(Max));
Insert INTO @sa(var1) VALUES (N’A-B-C’);
Insert INTO @sa(var1) VALUES (N’D-E-F’);
Insert INTO @sa(var1) VALUES (N’G-H-I’);
Insert INTO @sa(var1) VALUES (N’J-K-I’);

SELECT * FROM tblTemp 
WHERE colA IN (SELECT var1 FROM @sa);

so i got the following error message.

The data types ntext and nvarchar(max) are incompatible in the equal to operator.

I still need to find for colB and colC. How should write query for this kind of situation?

all suggestions are welcome.

Upvotes: 0

Views: 87

Answers (3)

Karl Kieninger
Karl Kieninger

Reputation: 9129

Since all suggestions are welcome.

How about change the datatype on tblTemp to NVARCHAR(MAX)?

NTEXT was deprecated with the introduction of NVARCHAR(MAX) in 2005.

ALTER TABLE tblTemp ALTER COLUMN colA NVARCHAR(MAX)

Upvotes: 0

TT.
TT.

Reputation: 16137

CAST/CONVERT (msdn.microsoft.com) your var1 to NTEXT type in your query so that the types are compatible.

SELECT
  *
FROM
  tblTemp
WHERE
  colA IN (
    SELECT 
      CAST(var1 AS NTEXT)
    FROM
      @sa
  );

Upvotes: 1

Dave C
Dave C

Reputation: 7392

You have to convert/cast your search term as an appropriate data type, in this case text.

Try this:

Declare @sa TABLE(var1 nvarchar(Max));
Insert INTO @sa(var1) VALUES (N’A-B-C’);
Insert INTO @sa(var1) VALUES (N’D-E-F’);
Insert INTO @sa(var1) VALUES (N’G-H-I’);
Insert INTO @sa(var1) VALUES (N’J-K-I’);

SELECT * 
FROM tblTemp t
WHERE EXISTS (SELECT 1 
              FROM @sa s 
              WHERE t.colA like cast('%'+s.var1+'%' as text)
                 OR t.colB like cast('%'+s.var1+'%' as text)
                 OR t.colC like cast('%'+s.var1+'%' as text)
             );

Upvotes: 0

Related Questions