I have seen a very common question like, Whats the difference between len and datalength function in SQL Server? (Important Interview question too... )
I have created a table name 'VENKATTABLE'
CREATE TABLE VENKATTABLE(NAME VARCHAR(100))
INSERT INTO VENKATTABLE VALUES('VENKAT ')
SELECT * FROM VENKATTABLE
Now, I've tried to use these statements,
SELECT LEN(NAME) FROM VENKATTABLE -- OUTPUT =>6
SELECT DATALENGTH(NAME) FROM VENKATTABLE -- OUTPUT =>9
The difference is length will trim the data and it wont consider the spaces available. Whereas, Datalength will fetch the exact data available and it will consider the spaces too.
In this case, I've tried with Ltrim and Rtrim to achieve the same functionality of len function.
SELECT DATALENGTH(LTRIM(RTRIM(NAME))) FROM VENKATTABLE -- OUTPUT =>6
Happy Learning!!!
Regards,
Venkatesan Prabu .J
Hi,
ReplyDeleteYour Blog is Good. Keep up the good work by posting interesting technical articles.
I suggest you to link your blog to Feedburner(http://www.feedburner.com), so that interested people can subscribe and receive your articles in their mail box whenever you post a new article.
mostly correct - except Len does an RTrim (of trialing spaces) and alos it depends on the width of the string being tested. So:
ReplyDeletedeclare @t nvarchar(20)
set @t = ' 1 2 3 '
select LEN(@t)
select DATALENGTH(@t)
gives
6
and
14
as char width is 2 bytes