SQL LENGTH Vs DATALENGTH Function

These algorithms LEN() and DATALENGTH() are used for completely different things. Many people, however, are unsure which one to use when.

By contrasting the SQL Serer methods LEN() and DATALENGTH , the goal of this essay is to clear up all of these misunderstandings .

SQL LENGTH Function

LEN() returns the number of characters in a string expression in SQL Server. After deleting the trailing spaces, it returns the length.

Example:LENGTH function returns how many number of character are in given input.

Sql-server, Ms Access

SELECT LEN('Difference') AS 'Total Characters';

MySql,sqlite

SELECT LENGTH('Difference') AS 'Total Characters';

Output: The output of above query is

'total characters'
10

SQL DATALENGTH Function

The DATALENGTH function calculates a variable's size in bytes.

It also takes into account the amount of space available. It's a data type function, which means it considers the input expression of the datatype.

DATALENGTH() returns the number of bytes utilised by any expression/column with a data type of NVARCHAR (UNICODE character string), in this example two bytes for each character.

It means that the DATALENGTH of a UNICODE character string is the number of characters plus spaces multiplied by two.

Example:DATALENGTH function

Sqlserver

SELECT DATALENGTH(' Statement ');

In above query returns the number of characters and also trailing spaces from input. This function only accept by sql-server database

Output: The output will be

Column1
11

SQL Difference Between DATALENGH And LENGTH Function

Here we discussed main distinguish in length & datalength function are tabulated :-

LENGTH	DATALENGTH
Returns the number of characters in a String with LEN().	The function DATALENGTH() returns the number of bytes used by any expression or column.
The data types `TEXT` and `IMAGE` are not allowed as input parameters.	Because varchar, varbinary, text, picture, nvarchar, and ntext data types may contain variable-length data, DATALENGTH() is especially useful with these data types.
A string expression can be a constant, variable, or column of character or binary data as an input argument.	An expression of any data type can be used as an input parameter.
`BIGINT` if expression is of the varchar(max), nvarchar(max) OR varbinary(max) data types; otherwise, `INT`.	`BIGINT` if expression is of the varchar(max), nvarchar(max) OR varbinary(max) data types; otherwise, `INT`.
It's a string function, which means it treats the input expression like a string. An integer value, for example, will be treated as a string and the number of characters will be returned. Output 2 will be returned if you do the following. `LECT LEN(25) AS [LEN]`	It's a data type function, which means it takes into account the datatype's input expression. An Integer value, for example, will be treated as an Integer and its size in bytes will be returned. The following command will produce output 4, which is the size of an integer. `SELECT DATALENGTH(25) AS [DATALENGTH]` .
When determining the amount of characters in an input expression, blank spaces at the end are ignored. For instance, the following will provide output 13. `SELECT LEN('Mudassar Khan ') AS [LEN]`	When determining the size of the input expression, trailing blank spaces are taken into account. For instance, the following will provide output 16. `SELECT DATALENGTH('Mudassar Khan ') AS [DATALENGTH]` .Because DATALENGTH considers each blank space to be one byte, the aforementioned behaviour occurs.

All Tutorials