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