mysql SELECT string with last letter '%n' not working-CodePudding

I want mysql to show me a table where the last string of a column has a specific letter

SELECT * FROM myTable WHERE col LIKE '%n';

nothing is going displayed (0 rows displayed).

But this one works

SELECT * FROM myTable WHERE col LIKE 'L%';

So when looking for the beginning of a string it will give me an output, but when looking for the end of a string it won't. I also tried it with other columns and it did not work.

Why?

The word it is looking for is London

The table was created like this (found this sample on a webpage):

CREATE TABLE IF NOT EXISTS `company` (
  `COMPANY_ID` varchar(6) NOT NULL DEFAULT '',
  `COMPANY_NAME` varchar(25) DEFAULT NULL,
  `COMPANY_CITY` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`COMPANY_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `company`
--

INSERT INTO `company` (`COMPANY_ID`, `COMPANY_NAME`, `COMPANY_CITY`) VALUES
('18', 'Order All', 'Boston\r'),
('15', 'Jack Hill Ltd', 'London\r'),
('16', 'Akas Foods', 'Delhi\r'),
('17', 'Foodies.', 'London\r'),
('19', 'sip-n-Bite.', 'New York\r');

CodePudding user response：

Your sample data shows that you have a carriage return character (\r) as the last character in the string. If we eliminate that from the search, the remaining string does match.

mysql> SELECT * FROM company where TRIM('\r' from company_city) LIKE '%n';
 ------------ --------------- -------------- 
| COMPANY_ID | COMPANY_NAME  | COMPANY_CITY |
 ------------ --------------- -------------- 
      |      | Order All     | Boston
      |      | Jack Hill Ltd | London
      |      | Foodies.      | London
 ------------ --------------- --------------

I recommend not to store trailing whitespace characters in your strings. Take care of newlines and carriage returns in your application presentation, not in the data.