CODEWITHSUNDEEP
sqlsql-likecase-sensitivecase-insensitive
Vas Theo
Vas Theo

Reputation: 39

SQL NOT LIKE incasesensitive for 'A', 'E', 'R', 'T'

Write a SQL statement that displays all columns from movies in the movie table. Only display movies where the title does not contain any of these letters: A E R T (any case)

I've attempted to write the query as

SELECT '103040698' as StudID, MovieNo, Title,RelYear, RunTime, RatingCode, ColourCode, TMDB_Score, TMDB_Votes, IMDB_ID
FROM Movie0698
WHERE NOT Title  LIKE '%A%'
(or NOT Title LIKE '%E%')
(or NOT Title LIKE '%R%')
(or NOT Title LIKE '%T%')

or even

SELECT '103040698' as StudID, MovieNo, Title,RelYear, RunTime, RatingCode, ColourCode, TMDB_Score, TMDB_Votes, IMDB_ID
FROM Movie0698
WHERE NOT Title LIKE ('%[aA][eE][rR][tT]%')

or even

SELECT '103040698' as StudID, MovieNo, Title,RelYear, RunTime, RatingCode, ColourCode, TMDB_Score, TMDB_Votes, IMDB_ID
FROM Movie0698
WHERE (NOT Title LIKE '%a%' AND NOT Title LIKE '%A%')
OR (NOT Title LIKE '%e%' AND NOT Title LIKE '%E%')
OR (NOT Title LIKE '%r%' AND NOT Title LIKE '%R%')
OR (NOT Title LIKE '%t%' AND NOT Title LIKE '%T%')
ORDER BY  Title DESC;

but either way, I still get results of movies that do have one of these letters in capital or lowercase in its title

by the way, I'm using SQLjunior which I believe is case sensitive

Upvotes: 0

Views: 93

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521259

You appear to be using SQL Server, in which case you can just use a single condition in your WHERE clause with enhanced LIKE:

SELECT '103040698' AS StudID, MovieNo, Title,RelYear, RunTime, RatingCode,
       ColourCode, TMDB_Score, TMDB_Votes, IMDB_ID
FROM Movie0698
WHERE Title NOT LIKE ('%[aAeErRtT]%');

There are other similar approaches depending on your database. For example, on MySQL you could use:

WHERE Title NOT REGEXP '[aAeErRtT]'

If none of these are available, then you will have to use the approach in the @Trincot answer.

Upvotes: 1

trincot
trincot

Reputation: 350272

You need a logical AND, not a logical OR. With an OR, it suffices that one of those conditions is true, but you need all of them to be true at the same time for the same record.

So:

WHERE UPPER(Title) NOT LIKE '%A%' 
  AND UPPER(Title) NOT LIKE '%E%'
  AND UPPER(Title) NOT LIKE '%R%'
  AND UPPER(Title) NOT LIKE '%T%'

The actual syntax to use for upper casing the string depends on the database engine you are using. So replace UPPER with what your database supports.

Upvotes: 2

Related Questions