CODEWITHSUNDEEP
mysqlsql
DaveL
DaveL

Reputation: 339

MySQL SELECT WHERE LIKE escaped data

I have the following table:

--
-- Table structure for table `DESTINATIONS`
--

CREATE TABLE IF NOT EXISTS `DESTINATIONS` (
  `DEST_ID` int(7) NOT NULL auto_increment,
  `COUNT_ID` int(6) NOT NULL,
  `REG_ID` int(6) NOT NULL,
  `CITY_ID` int(6) NOT NULL,
  `CREATE_DATE` datetime NOT NULL,
  `DESTINATION` varchar(256) NOT NULL,
  `APPROVED` tinyint(1) NOT NULL,
  PRIMARY KEY  (`DEST_ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=192 ;

--
-- Dumping data for table `DESTINATIONS`
--

INSERT INTO `DESTINATIONS` (`DEST_ID`, `COUNT_ID`, `REG_ID`, `CITY_ID`, `CREATE_DATE`, `DESTINATION`, `APPROVED`) 
VALUES (166, 9, 818, 0, '2011-10-27 15:24:20', 'Freeman\\''s Bay', 0);

And I am trying to run the following query:

SELECT * 
FROM  `DESTINATIONS` 
WHERE  `DESTINATION` LIKE  '%Freeman\'s Bay%'

which is returning 0 results. However, stripping the LIKE clause to anything BEFORE the quote mark will find and return a match.

Obviously, I'm doing something wrong, but I haven't been able to figure out what exactly. Can I not perform a SELECT LIKE this? Have I escaped the quotes incorrectly? Any input is appreciated!

Upvotes: 0

Views: 182

Answers (4)

Turophile
Turophile

Reputation: 3405

Have you tried using a repeated quote instead of escaping it? Thus: 'Freeman''s Bay'

Use the same syntax in both places.

Upvotes: 0

GolezTrol
GolezTrol

Reputation: 116100

The insert is escaped incorrectly. You are actually inserting the value Freeman\'s Bay, including the \. The select is okay.

The select is escaped incorrectly. You are have inserting the value Freeman\'s Bay, but you're looking for Freeman's Bay, excluding the \.

Upvotes: 2

Scott Hunter
Scott Hunter

Reputation: 49803

Did you try writing the LIKE string as it appears in the INSERT statement, but with a % on either end?

Upvotes: 0

Joe
Joe

Reputation: 82564

'%Freeman\'s Bay%' and 'Freeman\\''s Bay' are even visibly different (obviously excluding %). If you fixed whichever one you want to be your format you will be good

Upvotes: 1

Related Questions