Returning all records that DO NOT contain a 4-character string...

Returning all records that DO NOT contain a 4-character string...

Post by whatagee » Sat, 20 May 2006 09:22:38


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
 
 
 

Returning all records that DO NOT contain a 4-character string...

Post by Tom Coope » Sat, 20 May 2006 10:20:48

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

 
 
 

Returning all records that DO NOT contain a 4-character string...

Post by whatagee » Sat, 20 May 2006 13:17:06

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