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

19 May 2006 12:22 AM

whatageek@gmail.com

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

19 May 2006 1:20 AM

Tom Cooper

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

<whatag***@gmail.com> wrote in message
Show quote

news:1147998157.992704.215630@j33g2000cwa.googlegroups.com...
> 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
>

19 May 2006 4:17 AM

whatageek@gmail.com

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