By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,164 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

Data checking?

P: n/a
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

Jul 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On 6 Jan 2005 06:13:08 -0800, ro**********@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)
Jul 23 '05 #2

P: n/a
Roy
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!!!

Jul 23 '05 #3

P: n/a
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.
Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.