T-SQL, Find numeric values

Question

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

The question is as follows: In Product table, determine the models which consist only of digits or only of latin letters (A-Z, case insensitive). Result set: model, type of model.

And I gave the correct answer which is:

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

Now my question is why do I need double negations to make it work. If I rewrite the code to:

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

I get the wrong answer: Your query returned the correct dataset on the first (available) database, but it returned incorrect dataset on the second checking database. * Wrong number of records (more by 37)

How come that the first example of code gives the correct answer while the second example doesn´t?

I have tried to find answer but no luck. Grateful for an explanation.

Martin Smith · Accepted Answer · 2015-11-08 14:45:46Z

6

 Where Model LIKE '%[A-Z]%' Or Model LIKE '%[0-9]%'

Matches rows where Model contains at least one alpha numeric character.

This does not exclude in any way those values that contain mixed alphanumeric and non-alphanumeric characters.

e.g. ~A#- would pass because of the presence of the A

Moreover your correct query matches either

'%[^A-Z]%': those strings which do not contain any non letters (i.e. consist of only letters or are empty)
'%[^0-9]%': those strings which do not contain any non digits (i.e. consist of only digits or are empty).

This is not handled at all in your second attempt and a mixed string of letters and digits would be accepted by that.

I would use your first attempt but if you were determined to avoid the double negative you could use

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

edited Nov 8, 2015 at 14:45

answered Nov 8, 2015 at 14:14

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

T-SQL, Find numeric values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related