MySQL returning only 1 row, although it should more

Question

I have the following table.

Name    Surname Age
John    Gordon  22
Abbey   Gibson  43
Claude  Dickson 34
Jean    Huston  54
Georges Oak     35
Georges Lane    32
Cathy   Harper  12
Joseph  Gordon  11
Chloe   Lane    23

I am trying to run the following query:

Select * from MyDB.myTable where myTable.Surname LIKE '%on'

Assumable, this should return to me all the rows that have their surnames ending with on, however, I only get 1 row return.

Can you please attache the table definition ? Is there any trigger/stored procedure ecc. ? — farbiondriven, Feb 15 '18 at 16:10
what happens when you do like '%son' or you do '%ton' or '%don' — isaace, Feb 15 '18 at 16:12
I mean Surname is varchar ? I just tested with a varchar(100) with your test data and it worked. — farbiondriven, Feb 15 '18 at 16:13
How was the table populated? My guess is through an application or import, (something other than hardcoded values that you control) and there are hidden characters in your data. — Aaron Dietz, Feb 15 '18 at 16:17
Agree with @AaronDietz I was just looking at this post to see how you would determine that: https://stackoverflow.com/questions/15442867/check-if-field-contains-special-character-in-sql — Kevin Pimentel, Feb 15 '18 at 16:21
if you use `Like %on%` do you get the missing names and more? if so then you have non-display characters at the end you need to handle. Nikhil's response may work; or maybe you have tabs or carriage return [char(10)]+linefeed [Char(13)] you need to handle... or other characters... — xQbert, Feb 15 '18 at 17:07
The rows are being imported back from an sql export using ---- Surname varchar(50) character set utf8 collate utf8_bin NOT NULL,----- Does it make any difference? — tony9099, Feb 15 '18 at 19:28

score 1 · Answer 1 · answered Feb 15 '18 at 17:01

1

try using trim() function maybe because of spaces remaning rows are not being fetched.

Select * from MyDB.myTable where trim(myTable.Surname) LIKE '%on';

answered Feb 15 '18 at 17:01

Nikhil Shetkar

346
1
9

trim will remove spaces if any from the end of surname – Khan M Feb 15 '18 at 17:02

score -4 · Answer 2 · edited Feb 15 '18 at 17:59

-4

Try this: //tested with a varchar(100) SELECT * FROM myTable where myTable.Surname like '%on'

edited Feb 15 '18 at 17:59

Andrew Brēza

7,705
3
34
40

answered Feb 15 '18 at 16:17

coder001

110
7

3

Add an explanation that explains why this will work and why OP's current code isn't working. – Aaron Dietz Feb 15 '18 at 16:18

MySQL returning only 1 row, although it should more

2 Answers2