SQL Server 2008 Empty String vs. Space

by

varchars and equality are thorny in TSQL. The LEN function says:

Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.

You need to use DATALENGTH to get a true byte count of the data in question. If you have unicode data, note that the value you get in this situation will not be the same as the length of the text.

print(DATALENGTH(' ')) --1
print(LEN(' '))        --0

When it comes to equality of expressions, the two strings are compared for equality like this:

  • Get Shorter string
  • Pad with blanks until length equals that of longer string
  • Compare the two

It’s the middle step that is causing unexpected results – after that step, you are effectively comparing whitespace against whitespace – hence they are seen to be equal.

LIKE behaves better than = in the “blanks” situation because it doesn’t perform blank-padding on the pattern you were trying to match:

if '' = ' '
print 'eq'
else
print 'ne'

Will give eq while:

if '' LIKE ' '
print 'eq'
else
print 'ne'

Will give ne

Careful with LIKE though: it is not symmetrical: it treats trailing whitespace as significant in the pattern (RHS) but not the match expression (LHS). The following is taken from here:

declare @Space nvarchar(10)
declare @Space2 nvarchar(10)

set @Space=""
set @Space2 = ' '

if @Space like @Space2
print '@Space Like @Space2'
else
print '@Space Not Like @Space2'

if @Space2 like @Space
print '@Space2 Like @Space'
else
print '@Space2 Not Like @Space'

@Space Not Like @Space2
@Space2 Like @Space

More Related Contents:

  1. Comma separated results in SQL
  2. How to check if a column exists in a SQL Server table
  3. Understanding PIVOT function in T-SQL
  4. Using T-SQL, return nth delimited element from a string
  5. Get first day of week in SQL Server
  6. Using RegEx in SQL Server
  7. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  8. Confused about UPDLOCK, HOLDLOCK
  9. INSERT INTO vs SELECT INTO
  10. How do I use the CONCAT function in SQL Server 2008 R2?
  11. How to set a default value for an existing column
  12. Select / Insert version of an Upsert: is there a design pattern for high concurrency?
  13. TSQL: How to convert local time to UTC? (SQL Server 2008)
  14. SQL Server: combining multiple rows into one row
  15. How do I find a stored procedure containing ?
  16. SQL Recursive CTE: Finding objects linked by property
  17. How to convert number of minutes to hh:mm format in TSQL?
  18. Convert decimal time to hours and minutes
  19. SQL Server 2008 – How do i return a User-Defined Table Type from a Table-Valued Function?
  20. How do I move a table into a schema in T-SQL
  21. Instead of trigger in SQL Server loses SCOPE_IDENTITY?
  22. How Can I Detect and Bound Changes Between Row Values in a SQL Table?
  23. How to wait for 2 seconds?
  24. What are row, page and table locks? And when they are acquired?
  25. Generate MD5 hash string with T-SQL
  26. What is the significance of 1/1/1753 in SQL Server?
  27. Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
  28. Insert Update trigger how to determine if insert or update
  29. Check if a row exists, otherwise insert
  30. How can I insert binary file data into a binary SQL field using a simple insert statement?

Leave a Comment