Thursday, May 10, 2012

Diffrerence Between LEN and DATALENGTH in SQL Server

LEN function removes trailing spaces of string while DATALENGTH consider trailing spaces.

Below is Example.

CREATE TABLE #tmp (h1 varchar(10))
GO
INSERT INTO #tmp VALUES ('abc ')
INSERT INTO #tmp VALUES ('abc')
GO
SELECT LEN(h1) as 'LEN(EqualWithSpace)',DATALENGTH(h1) as 'DATALENGTH(EqualWithSpace)', * FROM #tmp WHERE h1 = 'abc '
SELECT  LEN(h1) as 'LEN(LikeWithSpace)', DATALENGTH(h1) as 'DATALENGTH(LikeWithSpace)', * FROM #tmp WHERE h1 LIKE 'abc %'
SELECT  LEN(h1)as 'LEN(LikeNoSpace)', DATALENGTH(h1) as 'DATALENGTH(LikeNoSpace)', * FROM #tmp WHERE h1 LIKE 'abc%'

GO
DROP TABLE #tmp 
 

This is the output of above select statements.


No comments:

Post a Comment