Data checking?

Hey all,
prolly a simple solution, but why isn't the following string working in
my execute sql step within DTS? It produces results, just not the ones
I want... What am I doing wrong?

select x from new_files where x like '%[^0-9]%' and x like '%[^a-z]%'

It's displaying all the records? It should only be displaying those
records that do *not* contain letters or numbers.
Thanks in advance!
-RoyOn 6 Jan 2005 06:13:08 -0800, roy.anderson@.gmail.com wrote:

> Hey all,
> prolly a simple solution, but why isn't the following string working in
> my execute sql step within DTS? It produces results, just not the ones
> I want... What am I doing wrong?
>
> select x from new_files where x like '%[^0-9]%' and x like '%[^a-z]%'
> It's displaying all the records? It should only be displaying those
> records that do *not* contain letters or numbers.
> Thanks in advance!
> -Roy

Your clause is selecting rows where the x column contains at least one
character that is not a digit and also contain at least one character that
is not a letter. If you had a row where x was all letters, all digits, or
maybe all letters plus punctuation but no digits, etc., then it would not
be included.

The clause you want is probably

WHERE NOT (x LIKE '%[0-9a-z]%')

(parenthesis optional)|||Thanks much Ross, after some toying around, the end product that works
is:

WHERE (x LIKE '%[^0-9a-z]%')

I'm unsure why having the "NOT" specified beforehand produces no
results, but it doesn't. I'm assuming it's because sqlserver perceives
the NOT as referring to the wildcards too, ergo, it's only looking for
blank fields.

Thanks much for the help!!!|||On 6 Jan 2005 09:02:41 -0800, Roy wrote:

> Thanks much Ross, after some toying around, the end product that works
> is:
> WHERE (x LIKE '%[^0-9a-z]%')
> I'm unsure why having the "NOT" specified beforehand produces no
> results, but it doesn't. I'm assuming it's because sqlserver perceives
> the NOT as referring to the wildcards too, ergo, it's only looking for
> blank fields.
> Thanks much for the help!!!

It looks to me like your query is requesting those rows that contain at
least one non-letter, non-digit character. I thought you wanted rows that
contained no letters and contained no digits... maybe I'm still confused
... but if you've got what you want, great.