Menu
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?
839
LEN() function calculates the number of characters of an input string, excluding the trailing spaces. It is an expression that can be a constant, variable, or column of either character or binary data. Returns : It returns the number of characters of an input string, excluding the trailing spaces.
Solution. To get the length of an ntext field, use DATALENGTH() instead of LEN() . Note that DATALENGTH will return the number of bytes, not the number of characters in the string. Each character in an ntext field is 2 bytes, so you need to take this into account when writing your query.
Solution. In addition to the LEN() function, SQL Server also has a DATALENGTH() function. This function can be used on all data types in your table. That's all there is to it.
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)))
54
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
