Post reply

Query puzzler

  • September 29, 2008 at 10:13 am

    #193343

    Scenario:

    I have a lookup table with an ID (int) and a Name (nvarchar(64))

    In that table are several entries which appear at first glance to have duplicate names, but in fact the second instance has two spaces appended to the name.

    In a view I join to this table using an inner join on the ID in another table.

    If I select from the view using the name of the lookup as a filter, and I choose one that has a "duplicate" in the lookup table, the query returns only those rows linked to the one without the appended spaces.

    If I include the spaces in the query, it returns nothing at all.

    I tried rebuilding all the statistics - no help.

    I couldn't believe this, so I created test tables with just the affected columns and ran a test and it would always return the rows of both the normal and the record with two spaces appended.

    My question is:

    How can this be?

    OR

    Is there a way I can select using that sort of filter, and have it behave in a way that is consistent with SQL.

    Thanks,

    Bill Mell

  • September 29, 2008 at 10:29 am

    #878506

    Are they spaces? Or are they non-printing characters?

    Try returning the ascii value of

    LEFT(REVERSE(yourstring), 1)

    and

    SUBSTRING(REVERSE(yourstring), 2, 1)

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • September 29, 2008 at 10:32 am

    #878508

    that's the solution . . . it was a linefeed character not a space.

    Thanks,

    Bill

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

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