• Richard Tatterton (6/24/2010)


    This is what I absolutely hate about SQL Server. A string is a f**king string, no matter whether it's got spaces on it at the end.

    Jeez.

    I'll bet you'd hate it more if it didn't offer such a simple way to include a specific number of trailing spaces in a match comparison.

    If I'm looking for all rows where firstname is 'John ' (with three trailing blanks), how would I code that? Comparing with "=" sure doesn't do it. Both of these queries return true:

    Declare @firstname varchar(10)

    set @firstname = 'John '

    select With_Spaces_EQ = case when

    @firstname = 'John '

    then 'True' else 'Not True' end

    Set @firstname = 'John'

    select No_Spaces_EQ = case when

    @firstname = 'John '

    then 'True' else 'Not True' end

    One way around this would seem be to add another condition teting DATALENGTH (assume @firstname is still defined):

    set @firstname = 'John '

    select With_Spaces_DataLen = case when

    @firstname = 'John ' and DATALENGTH(@firstname) >= 7

    then 'True' else 'Not True' end

    Set @firstname = 'John'

    select No_Spaces_DataLen = case when

    @firstname = 'John ' and DATALENGTH(@firstname) >= 7

    then 'True' else 'Not True' end

    ... but that seems rather strained and/or clumsy.

    I'd rather code a pattern match with LIKE:

    Set @firstname = 'John '

    select With_Spaces_Like = case when

    @firstname LIKE 'John '

    then 'True' else 'Not True' end

    Set @firstname = 'John'

    select No_Spaces_Like = case when

    @firstname LIKE 'John '

    then 'True' else 'Not True' end