Developer Forum

Hello,

I am trying to query a NTEXT field for a 4-character string and return
only those records that DO NOT contain this string. I have converted
the NTEXT field to VARCHAR for this purpose, but the query still
returns records with the string.

(NOT(UPPER(CAST(A.COMMENT AS varchar(8000))) IN (N'%FC80%',
N'%UP50%')))

Can someone please provide some insight into why this statement isn't
doing what I want it to do. I am still relatively new to T-SQL, so any
advice would be much appreciated.

Thanks in advance,

Dave

IN doesn't recognize wild card characters (like %). You want to use
PATINDEX which has the additional advantage of working with NTEXT fields
directly without converting to varchar.

Something like
WHERE PATINDEX(N'%FC80%', A.COMMENT) = 0 AND
PATINDEX(N'%UP50%', A.COMMENT) = 0

Tom

Hi Tom,

Thanks for your reply.

Sounds good. I vaguely remember hearing about PATINDEX. I'll study up
on it and give it a try once I'm back at my desk.

Thanks again,

Dave