Published on

Authors

# Problem

The error message "Argument data type ntext is invalid for argument 1 of len function" occurs when you are trying to use the LEN() function on a column or variable of data type ntext in SQL Server. The LEN() function only works on data types nvarchar and varchar.

To solve this error, you can either convert the data type of the column or variable to nvarchar or varchar using the CAST() or CONVERT() function, or you can use the DATALENGTH() function instead of LEN() function.

Here is an example of how to use the CAST() function to convert the data type of a column called "ntext_column" to nvarchar before using the LEN() function:

Solution 1


DECLARE @ntext_column ntext
SET @ntext_column = 'example'
DECLARE @length INTSET @length = LEN(CAST(@ntext_column AS nvarchar(max)))
PRINT @length



Alternatively, here is an example of how to use the DATALENGTH() function to get the number of bytes used to represent an ntext column:

Solution 2



DECLARE @ntext_column ntext
SET @ntext_column = 'example'
DECLARE @length INTSET @length = DATALENGTH(@ntext_column)
PRINT @length



Note that DATALENGTH returns the number of bytes used to represent the data, whereas LEN returns the number of characters. If you are dealing with multi-byte characters (such as unicode), then DATALENGTH will give you the correct length while LEN may not.

:)