SQL query returning empty set

Question

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.

Are you after 'LIKE' ?

Strawberry
– Strawberry

2017-05-01 07:03:18 +00:00
Commented May 1, 2017 at 7:03 — Strawberry
– Strawberry, Commented May 1, 2017 at 7:03
you should use like

krishn Patel
– krishn Patel

2017-05-01 07:06:01 +00:00
Commented May 1, 2017 at 7:06 — krishn Patel
– krishn Patel, Commented May 1, 2017 at 7:06
It's the same @Strawberry.

Skeptical Ant
– Skeptical Ant

2017-05-01 07:06:09 +00:00
Commented May 1, 2017 at 7:06 — Skeptical Ant
– Skeptical Ant, Commented May 1, 2017 at 7:06
Query 3 would work with another ) after booktitle)

P.Salmon
– P.Salmon

2017-05-01 07:10:04 +00:00
Commented May 1, 2017 at 7:10 — P.Salmon
– P.Salmon, Commented May 1, 2017 at 7:10

krishn Patel · Accepted Answer · 2017-05-01 07:09:29Z

2

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%';

edited May 1, 2017 at 7:09

answered May 1, 2017 at 7:06

krishn Patel

2,5991 gold badge22 silver badges31 bronze badges

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

2 Comments

Skeptical Ant Over a year ago

It worked , but can you please explain why it did or link me to the sql manual i would appreciate it.

krishn Patel Over a year ago

you can find simple use here w3schools.com/sql/sql_like.asp

itzmukeshy7 · Accepted Answer · 2017-05-01 07:18:58Z

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;

Collectives™ on Stack Overflow

SQL query returning empty set

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related