SUBSTRING, LEN and DATALENGTH
Today I was talking with a colleague asking a question regarding how the SUBSTRING function works in TSQL. The question was, “What happens if I specify a length greater than the number of characters in the string?” My response was that SQL Server doesn’t require the exact length for the number of bytes; so, if you specify more bytes than those available in the string it simply stops when it hits the end of the string.
For example,
SELECT SUBSTRING(‘ABCDE’, 4, 10) will not throw an error even though there are not 10 characters. It simply returns ‘DE’.
At that point we started talking about how to return the length of a string. SQL Server supports two different functions returning the length, DATALENGTH and LEN. I knew about both functions, but not if/how they function differently, and decided to look it up and write about it tonight. With a little searching on Google I came across an example that clarifies the difference quite nicely.
Here is an excerpt from my google search…http://stackoverflow.com/questions/558102/in-sql-server-2005-what-is-the-difference-between-len-and-datalength
SELECT LEN('string'), LEN('string '), DATALENGTH('string'), DATALENGTH('string '), LEN(N'string'), LEN(N'string '), DATALENGTH(N'string'), DATALENGTH(N'string ')
Will return 6, 6, 6, 7, 6, 6, 12, 14
That pretty much demonstrates the different behaviors of the two functions. Based on those results you should be able to determine which method meets your needs when getting the length of a string.
Cheers,
Ben