Problem with SELECT

[LeedsLad]

I have a table, tblConductor, with fields CondID, CondSURNAME, CondOTHERNAMES, CondBORN, CondDIED, CondNATIONALITY.  This table has 3 records (identical except for CondID which is the primary key).  If I run the query

 

SELECT * FROM tblConductor

 

the result id three records as I would expect.  When I run the query

 

SELECT * FROM tblConductor WHERE CondSURNAME = 'Britten'

 

the result is just 1 record which happens to be the third record in the table.

 

It seems to me that the two queries in this case are identical (all three records have CondSURNAME = 'Britten') so why is the outcome different?

[kicken]

The other two records must have some difference in the value for CondSURNAME. Perhaps extra spaces around the word, or other invisible characters. Perhaps they are a different case and you're using a case-sensitive collation.

 

Run this query to check the values:

SELECT HEX(CondSURNAME) FROM tblConductor

If all three rows are truly identical that will return three identical rows. If there is a difference in spacing, you will see different values.

[LeedsLad]

Thank you very much.  There is a difference so now I have to try to work out what.  Two of the apparently identical rows return value 204272697474656E and the other returns 4272697474656E.

[kicken]

Those strings are a sequence of hex codes, each pair of characters represents one of the original characters. You can see that the only difference between the two strings is the extra 20 at the front:

204272697474656E 
  4272697474656E

If you check the ascii chart (Hx column) you can see that hex code 20 corresponds to a space character, meaning two of your rows have an extra space at the front of their value.