identical distinct values!!!

• balde

  SSCommitted

  Points: 1594

  More actions

  July 21, 2006 at 6:18 am

  #114807

  I'm stumped or going mad!

  The following query;

  select distinct mediacode1 from mediacodes where mediacode1 like '%Ultra Eyecare%'

  returns 2 records as follows (using Results to text option in QA);

  mediacode1

  ------------

  Ultra Eyecare

  Ultra Eyecare

  (2 row(s) affected)

  i.e. 2 identical (apparantly) strings of 'Ultra Eyecare'. If I perform an exact search for the string;

  select distinct mediacode1 from mediacodes where mediacode1 = 'Ultra Eyecare'

  only 1 record is returned ( as you would expect&nbsp

  How do I find out what the difference is between the 2 strings returned by the first "like" query -they appear to be identical in QA result panes, excel, Textpad - but SQL thinks they are different...

  any help would be appreciated

   

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  July 21, 2006 at 6:28 am

  #650647

  There must be Line Feed charachters in one of those strings.

  Select LEN(mediacode1) and you'll see the difference.

  _____________
  Code for TallyGenerator

• balde

  SSCommitted

  Points: 1594

  More actions

  July 21, 2006 at 6:37 am

  #650654

  and there it is for all to see...

  mediacode1,length

  --------------------

  Ultra Eyecare

  ,15

  Ultra Eyecare,13

  (2 row(s) affected)

  Many thanks Sergiy...

  LF's shouldn't really exist in this data, I guess someone must have paste them in by mistake via EM....

   

   

• Andrew Stanton

  Ten Centuries

  Points: 1115

  More actions

  July 24, 2006 at 9:21 am

  #651101

  Dont forget users copy and pasting from excel or access. Those two are buggers for adding 0A + 0D chars to data.