Sql where clause not working

Question

SQL where clause is not working in my database.

I have a table called "sites" and structure like that

id     site
1      xyz.com
2      google.com
3      example.com

I am running this SQL query

SELECT * FROM `sites` WHERE `site` = "google.com";

But I am getting this output

 MySQL returned an empty result set (i.e. zero rows). (Query took 0.0009 sec)

I never see before like that in my life.

Update: Screenshot

I do not want to apply this query in project.

SELECT * FROM `sites` WHERE `site` LIKE "%google.com%";

#

---

The real problem was in insert commands on creation of DB. Try

INSERT INTO sites (id, site) VALUES (1, '\nxyz.com\n'), (2, '\ngoogle.com\n'), (3, '\nexample.com\n')

and manually check records in the table. You would not see line breaks. This is an issue in SQL I've noticed.

Answer 1

My first answer:

Use single quotes, SELECT * FROMsitesWHEREsite= 'google.com';

In regards to the single/double quote syntax, it depends on your SQL Mode. From the MySQL documentation:

The ANSI_QUOTES mode causes the server to interpret double-quoted strings as identifiers. Consequently, when this mode is enabled, string literals must be enclosed within single quotation marks. They cannot be enclosed within double quotation marks. The server SQL mode is controlled as described in Section 5.1.6, “Server SQL Modes”.

My second answer after OP's edit:

After seeing the screenshots, it looks like you have a blank line above the URL's in your database or whitespace. You'll need to remove it manually or with TRIM.

To test if there is whitespace, you can use a wildcard:

SELECT `site` FROM `sites` WHERE `site` LIKE '%google.com%'

If you get a result you know there's whitespace. if you want to workaround the issue without permanently removing the whitespace:

SELECT TRIM(site) FROM `sites` WHERE `site` LIKE '%google.com%'

To permanently remove the whitespace from only one row:

UPDATE `sites` SET `site` = TRIM(site) WHERE `site` LIKE '%google.com%'

To permanently remove the whitespace from all rows (backup table first), you can do:

UPDATE `sites` SET `site` = TRIM(site)