sql server - SQL Query Finding From Table DataType Declaration -

sql server - SQL Query Finding From Table DataType Declaration -

i got serial keys find in sql database, such “a-b-c”,”d-e-f”,”g-h-i”,”j-k-l” , stored in tbltemp using ntext info type. these above keys may store in 3 columns, cola, colb , colc (sometimes store in 1 column , rest null). sometimes, 2 serial keys can find in 1 column (e.g. a-b-c;d-e-f) using “;” seperated. wrote next sql query.

declare @sa table(var1 nvarchar(max)); insert @sa(var1) values (n’a-b-c’); insert @sa(var1) values (n’d-e-f’); insert @sa(var1) values (n’g-h-i’); insert @sa(var1) values (n’j-k-i’); select * tbltemp cola in (select var1 @sa);

so got next error message.

the info types ntext , nvarchar(max) incompatible in equal operator.

i still need find colb , colc. how should write query kind of situation?

all suggestions welcome.

you have convert/cast search term appropriate info type, in case text.

try this:

declare @sa table(var1 nvarchar(max)); insert @sa(var1) values (n’a-b-c’); insert @sa(var1) values (n’d-e-f’); insert @sa(var1) values (n’g-h-i’); insert @sa(var1) values (n’j-k-i’); select * tbltemp t exists (select 1 @sa s t.cola cast('%'+s.var1+'%' text) or t.colb cast('%'+s.var1+'%' text) or t.colc cast('%'+s.var1+'%' text) );

sql sql-server tsql