T-SQL, Find Numeric Values

Question

I did this quiz on http://www.sql-ex.ru/ , question 35, to be exact.

The question is this: In the Product table, define models that consist only of numbers or only Latin letters (AZ, case insensitive). A set of results: model, type of model.

And I gave the correct answer:

SELECT model, type FROM Product WHERE Model NOT LIKE '%[^AZ]%' OR Model NOT LIKE '%[^0-9]%' 

Now my question is why I need double negation to make it work. If I rewrote the code to:

 SELECT model, type FROM Product WHERE Model LIKE '%[AZ]%' OR Model LIKE '%[0-9]%' 

I get the wrong answer: Your query returned the correct data set to the first (available) database, but it returned the wrong data set to the second control database. * Incorrect number of entries (37 more)

How is it that the first code example gives the correct answer, and the second example does not work?

I tried to find the answer, but no luck. Thanks for the explanation.

Answer 1

  Where Model LIKE '%[AZ]%' Or Model LIKE '%[0-9]%' 

Matches strings where Model contains at least one alphanumeric character.

This in no way excludes those values ​​that contain mixed alphanumeric and non-alphanumeric characters.

eg. ~A#- will pass due to the presence of A

In addition, your correct request matches

• '%[^AZ]%' : those lines that do not contain any letters (i.e. consist only of letters or are empty)
• '%[^0-9]%' : those lines that do not contain any digits (i.e. consist only of digits or are empty).

This is not processed in the second attempt, and a mixed string of letters and numbers will be accepted by this.

I would use your first attempt, but if you decided to avoid double negation, you could use

 SELECT model FROM Product WHERE Model LIKE REPLICATE('[AZ]', LEN(Model)) OR Model LIKE REPLICATE('[0-9]', LEN(Model))