How do I count the number of characters in SQL server ntext (i.e. memo) field in an access query?

Question

I want to write an access query to count the characters in an ntext field in a linked SQL server table.

In SQL server, I would just use this command (which wont work in access):

 select datalength(nTextFieldName) //this command works on sql server but not in access 

In access, I can only find the len command, which wont work on ntext fields:

select len(nTextFieldName) // access says nText is not a valid argument to this function.

Googling around, I've found a bunch of posts saying to use len, which is giving me an error.

What is the command?

Xenomorphic

Answer 1

ntext type doesn't work with LEN. This specific type as well as a few others are deprecated:

ntext, text, and image data types will be removed in a future version of Microsoft SQL 
Server. Avoid using these data types in new development work, and plan to modify applications 
that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. For more 
information, see Using Large-Value Data Types.

The best way to handle this is to convert/cast the datatype to one that works such as varchar(max)/nvarchar(max) and only then get the LEN.

SELECT LEN(CAST(nTextFieldName As nvarchar(max)))

Sinistral

how would you determine if the converted text was truncated during conversion? – Preuss

LEN(CAST(nTextFieldName As nvarchar(max))) worked for me. – Guitarfish

I don't see why the conversion would truncate the string. Both ntext and nvarchar(max) can hold up to 2^30 - 1 characters. Sources: learn.microsoft.com/en-us/sql/t-sql/data-types/… learn.microsoft.com/en-us/sql/t-sql/data-types/… – Sternutation

Answer 2

select LENGTH(nTextFieldName) from table_name;

Randa