Post reply

String comparisons Am I going Mad?

  • March 4, 2009 at 11:07 am

    #200476

    The query below

    create table test

    (phrase varchar(100))

    insert into test values ('phrase')

    select * from test where phrase='phrase '

    The query above returns a result

    Why?

    My head is hurting

    Its got nothing to do with ansi_padding as this only is used in storage. How does sql do this comparison? I would expect 0 results

  • March 4, 2009 at 11:17 am

    #953446

    SQL ignores trailing spaces when doing string comparisons, as required by the SQL standard

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 4, 2009 at 11:17 am

    #953447

    for varchar strings, the = comparison operator strips out ending whitespace.

    that is different fromt eh LIKE operator...if you do LIKE 'phrase %', that would not find the item because of the space.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 4, 2009 at 11:21 am

    #953451

    Lowell (3/4/2009)

    for varchar strings, the = comparison operator strips out ending whitespace.

    Char and varchar both behave this way.

    create table #test

    (phrase char(10))

    insert into #test values ('phrase') -- will be padded out to 'phrase ' because it's a char column

    select * from #test where phrase='phrase '

    select * from #test where phrase='phrase'

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 4, 2009 at 3:09 pm

    #953640

    You can handle the problem by a little trick. Check the value AND the DATALENGTH (not then LEN). The DATALENGTH of the search criterion still contains the full size.

    DECLARE @test-2 TABLE (phrase VARCHAR(100))

    INSERT INTO @test-2 VALUES ('phrase')

    DECLARE @search NVARCHAR(100)

    SET @search = 'phrase '

    SELECT * FROM @test-2 WHERE phrase = @search AND DATALENGTH(phrase) = DATALENGTH(@search)

    Greets

    Flo

  • March 5, 2009 at 5:56 am

    #953945

    Thanks All

    All useful stuff 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

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