LEN function not including trailing spaces in SQL Server

by

This is clearly documented by Microsoft in MSDN at http://msdn.microsoft.com/en-us/library/ms190329(SQL.90).aspx, which states LEN “returns the number of characters of the specified string expression, excluding trailing blanks”. It is, however, an easy detail on to miss if you’re not wary.

You need to instead use the DATALENGTH function – see http://msdn.microsoft.com/en-us/library/ms173486(SQL.90).aspx – which “returns the number of bytes used to represent any expression”.

Example:

SELECT 
    ID, 
    TestField, 
    LEN(TestField) As LenOfTestField,           -- Does not include trailing spaces
    DATALENGTH(TestField) As DataLengthOfTestField      -- Shows the true length of data, including trailing spaces.
FROM 
    TestTable

More Related Contents:

  1. how to select next n letters after one specific letter using function in sql server by giving function name,specific letter, no of letters next
  2. SQL server query to output the data shown below
  3. Will ANSI JOIN vs. non-ANSI JOIN queries perform differently?
  4. What is the difference between Integrated Security = True and Integrated Security = SSPI?
  5. How to use a variable for the database name in T-SQL?
  6. What is the difference between char, nchar, varchar, and nvarchar in SQL Server?
  7. Missing artifact com.microsoft.sqlserver:sqljdbc4:jar:4.0
  8. Create a view with ORDER BY clause
  9. What does ‘COLLATE SQL_Latin1_General_CP1_CI_AS’ do?
  10. INSERT INTO vs SELECT INTO
  11. Possible to store value of one select column and use it for the next one?
  12. Date conversion and culture: Difference between DATE and DATETIME
  13. Why does select SCOPE_IDENTITY() return a decimal instead of an integer?
  14. How to remove accents and all chars a..z in sql-server?
  15. RODBC odbcDriverConnect() Connection Error
  16. Calling an API from SQL Server stored procedure
  17. Escape Character in SQL Server
  18. Is there a way to not use square brackets in SQL Server?
  19. Retrieve data from stored procedure which has multiple result sets
  20. How can I get the sum of multiple datetime values?
  21. Use of Begin / End Blocks and the Go keyword in SQL Server?
  22. How do you set autocommit in an SQL Server session?
  23. Ampersand (&) operator in a SQL Server WHERE Clause
  24. Avg of float inconsistency
  25. how to write number to word function in sql server [closed]
  26. Milliseconds wrong when converting from XML to SQL Server datetime
  27. Using GROUP BY with FIRST_VALUE and LAST_VALUE
  28. SQL Server 2008 can’t login with newly created user
  29. Update SQL with consecutive numbering
  30. Nested transactions in Sql Server

Leave a Comment