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

UPDATE: OP had invisible newline characters (\n) in his dataset. @EternalPoster (and I) supposed that Trim would remove all whitespace, but MySql Trim Documentation specifies leading & trailing spaces only.

---

This is what I did:

-- for http://stackoverflow.com/questions/27203169/sql-query-not-work-for-google-com
-- and http://stackoverflow.com/questions/27202157/sql-where-clause-not-working

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

-- --------------------------------------------------------

DROP TABLE IF EXISTS `sites`;

--
--  structure for table `sites`
--
CREATE TABLE IF NOT EXISTS `sites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `site` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- data for table `sites`
--
INSERT INTO `sites` (`id`, `site`) VALUES
(1, 'xyz.com'),
(2, 'google.com'),
(3, 'example.com');

--
-- select google
--
SELECT * 
FROM sites 
WHERE site = 'google.com'
;

--
-- select google
--
SELECT * 
FROM sites 
WHERE site = 'google.com'
;

and this is what I got:

So in my case, I see the script functioning as expected.

What's different about your case? My installation is a fairly default setup. The fact that Like '%google.com%' works on your dataset suggests a couple things. Folks have already suggested TRIM, because the Like expression would match invisible characters (spaces, tabs, backspaces, nulls). MySQL has a separate operator REGEXP for regular expressions, so it wouldn't seem to be that the . character is being used as a wildcard, but that might be worth a look.

Create an empty database and try running my script above. Do you get the same result I do?