CODEWITHSUNDEEP
mysql
Skeptical Ant
Skeptical Ant

Reputation: 384

SQL query returning empty set

I have this table

| BookID | BookTitle                      | NumberOfPages | NoOfCopies |
+--------+--------------------------------+---------------+------------+ 
|      1 | The Help                       |           444 |          4 |
|      2 | The Catcher in the Rye         |           277 |         10 |  
|      3 | Crime and Punishment           |           545 |          2 |
|      4 | The Brothers Karamazov         |           795 |          1 |
|      5 | A Crown of Wishes              |           369 |         12 |
|      6 | The Fireman                    |           752 |          3 |
|      7 | Fahrenheit 451                 |           174 |          9 |
|      8 | The Hobbit                     |           366 |          1 |
|      9 | Lord of Emperors               |           560 |          4 |
|     10 | Holy Bible: King James Version |          1590 |         11 |
----------------------------------------------------------------------------

When I insert a book title and expect it to return the book id, it always returns an empty set
so far, I have tried these queries.->book_info is the name of the table:

select BookID from book_info where ucase(BookTitle) = ' THE HELP% ';
select BookID from book_info where BookTitle = ' The Help   ';
select BookID from book_info where lcase(trim(BookTitle) = 'the help';

but none of them worked.


Note I don't rely on sql in my job.

Upvotes: 4

Views: 2114

Answers (2)

itzmukeshy7
itzmukeshy7

Reputation: 2677

The issue here is with the operator you are using and the value you are function you are expecting from it, = operator checks for the exact match that's why your queries are returning no records:

select BookID from book_info where ucase(BookTitle) = ' THE HELP% ';
select BookID from book_info where BookTitle = ' The Help   ';
select BookID from book_info where lcase(trim(BookTitle) = 'the help';

And one more thing that is:

MySQL queries are not case-sensitive by default.

So you don't need to add the string methods here to change the values case.

We usually use the % with LIKE only like this:

select BookID from book_info where ucase(BookTitle) LIKE '%THE HELP%';

In this query LIKE %THE HELP% will match all the string having THE HELP in them;

Upvotes: 2

krishn Patel
krishn Patel

Reputation: 2599

you need to use like if you want to use "%"

when you use "=" you need to sure it is same. even space also count

  select BookID from book_info where BookTitle LIKE 'THE HELP%';

Upvotes: 2

Related Questions