Menu
How do you find out the length/size of the data in an ntext column in SQL? - It's longer than 8000 bytes so I can't cast it to a varchar. Thanks.
288
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.
Use COL_LENGTH() to Get a Column's Length in SQL Server In SQL Server, you can use the COL_LENGTH() function to get the length of a column. More specifically, the function returns the defined length of the column, in bytes. The function accepts two arguments: the table name, and the column name.
You can get the MySQL table columns data type with the help of “information_schema. columns”. SELECT DATA_TYPE from INFORMATION_SCHEMA. COLUMNS where table_schema = 'yourDatabaseName' and table_name = 'yourTableName'.
Use DataLength()
SELECT * FROM YourTable WHERE DataLength(NTextFieldName) > 0 The clue's in the question: use DATALENGTH(). Note it has a different behaviour to LEN():
SELECT LEN(CAST('Hello ' AS NVARCHAR(MAX))),
DATALENGTH(CAST('Hello ' AS NVARCHAR(MAX))),
DATALENGTH(CAST('Hello ' AS NTEXT))
returns 5, 16, 16.
In other words, DATALENGTH() doesn't remove trailing spaces and returns the number of bytes, whereas LEN() trims the trailing spaces and returns the number of characters.
33
Select Max(DataLength([NTextFieldName])) from YourTable
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
