Post reply

identical distinct values!!!

  • 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

     

  • 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

  • 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....

     

     

  • 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.

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply