String Functions in SQL Server

1. LEN ( string or expression )

we know this function which retrives the length of the data in the column.

Consider a query,

SELECT LEN('sample text')

returns value 11

This function will work for Data types varchar(max), nvarchar(max) or varbinary(max), which return int or bigint value.

2. DATALENGTH ( expression )

This is function similar to LEN, but it is especially useful with text, image and ntext Data Types and also for varchar, varbinary and nvarchar Data Types.

The DATALENGTH of NULL is NULL.

we can use the LEN to find the length of string expression after casting or converting it to varchar, please note, using this may truncate real values.