CODEWITHSUNDEEP
sqlsql-serverwhitespace
Morvael
Morvael

Reputation: 3567

T-SQL Handling of White-Space

I have a user input form that due to my own foolish oversight allowed users to input empty strings as a means of identifying which set of data a new data set was related too...

This has led to multiple data sets being recorded with empty identifiers, or identifiers consisting of multiple "space" characters. These are indistinguishable to users as they all just appear as empty, however I wouldn't expect this to be a programmatic problem as the strings are different.

And yet when I try to retrieve that data sets I get all of them that have just white-space as the identifier.

Essentially the problem can be isolated as below, why does this query produce 4 rows of results for each SELECT, instead of just the one that actually matches?

DECLARE @testTable TABLE ([NumberOfSpaces] INT, [SpaceString] VARCHAR(50))

INSERT INTO @testTable ([NumberOfSpaces],[SpaceString]) VALUES
(0,''),
(1,' '),
(2,'  '),
(3,'   ')

SELECT * FROM @testTable WHERE [SpaceString] = ''
SELECT * FROM @testTable WHERE [SpaceString] = ' '
SELECT * FROM @testTable WHERE [SpaceString] = '  '
SELECT * FROM @testTable WHERE [SpaceString] = '   '

This is more for curiosity's sake, as my actual fix will need to remove the identifiers which only contain white-space.

Upvotes: 2

Views: 709

Answers (2)

HoneyBadger
HoneyBadger

Reputation: 15130

As I understand it, sql-server trims all trailing white space when used in comparisons/joins etc. This is actually ANSI standard. More info here.

Upvotes: 2

Sean Lange
Sean Lange

Reputation: 33571

You could add an additional predicate to your where clause to check for DATALENGTH.

declare @SpaceString varchar(10) = '  '
SELECT * 
FROM @testTable 
WHERE [SpaceString] = ''
and DATALENGTH(SpaceString) = DATALENGTH(@SpaceString)

Of course the becomes a nonSARGable predicate but you don't have much choice since the table allows this data in. Do be careful here as LEN and DATALENGTH as not quite the same. LEN will ignore trailing spaces but DATALENGTH will not.

Upvotes: 1

Related Questions