–DATALENGTH VS LEN — Differences

CREATE TABLE TESTLEN
(

id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
DATA TEXT,
FIELDSS VARCHAR(200)
)

INSERT INTO TESTLEN VALUES ( ‘dsfbsdfsdf’ , ’sdf nvbnvbn hsdfsdgvxcvdf’ )

SELECT DATALENGTH(DATA) , DATALENGTH(FIELDSS) , id FROM TESTLEN

SELECT LEN(DATA) , LEN(FIELDSS) , id FROM TESTLEN
–You cannot use len funtion for Text fields , you have to use datalength

SELECT LEN (‘DATA ‘) FROM TESTLEN
–This will trim spaces and then provide you the length

SELECT DATALENGTH (‘DATA ‘) FROM TESTLEN
–This will not trim spaces.

Note :
Return type for both is bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise int.

Other notes:
DATALENGTH is especially useful with varchar, varbinary, text, image,
nvarchar, and ntext data types because these data types can store
variable-length data.
The DATALENGTH of NULL is NULL.
THE LEN of NULL is NULL

Pawan Kumar
Pawankkmr@hotmail.com