2

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');
Schwern
  • 153,029
  • 25
  • 195
  • 336
Vapiano
  • 33
  • 5
  • 2
    Are you sure it isn't `"London "` or some other format that doesn't actually end in "n"? If you could create a dbfiddle to demonstrate a table that doesn't work, that would be helpful. – Bill Karwin Dec 06 '22 at 00:45
  • Yes I am sure. I have the same problem when trying it with other colomns. – Vapiano Dec 06 '22 at 00:46
  • 2
    It could be a collation issue. Not sure since you haven't provided the table DDL or a dbfiddle. – Bill Karwin Dec 06 '22 at 00:52
  • Okay thanks, first I have to find out how to provide the table DDL. – Vapiano Dec 06 '22 at 01:04
  • @Vapiano See https://stackoverflow.com/questions/1498777/how-do-i-show-the-schema-of-a-table-in-a-mysql-database – Schwern Dec 06 '22 at 01:08
  • I have edited my first post now. – Vapiano Dec 06 '22 at 01:13
  • Don't bother with DESCRIBE. It doesn't show the full table definition. I have no idea why so many people use DESCRIBE and seem not to know about SHOW CREATE TABLE. https://dev.mysql.com/doc/refman/8.0/en/show-create-table.html – Bill Karwin Dec 06 '22 at 01:13
  • @Vapiano Try `select hex(right(col, 1))` which will show the last character in hexadecimal. n is 6e. – Schwern Dec 06 '22 at 01:16

1 Answers1

2

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828