To measure the length of VARCHAR fields the function LEN(varcharfield) is useful.
To measure the length of TEXT fields the function is DATALENGTH(textfield). Len will not work for text field.
Example:
SELECT DATALENGTH(yourtextfield) AS TEXTFieldSize
Reference : Pinal Dave (https://blog.sqlauthority.com)
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
84 Comments. Leave new
Thanks a lot!
I want to increase the text size in one specific DB. I see that the maximum text field size I already have is around 32,000 characters but text with 40,000 characters was truncated.
How can i increase text size in specific DB or table?
Why do you need this? If you want to view full content, export to text file and view from there
The problem is not to VIEW the content BUT to insert the whole text. the text is not inserted completely and is truncated…
Sir can please you let me know that what is difference between LEN and DATALENGTH
Thank you!
Hello All
I want to fetch only 100 character from spacific column, plz tell me what is query for that.
select left(col,100) from table
I am sending the output of a query to a file and unfortunately every row is containing only a certain number of characters. My query has rows having length more than 10000 characters for each row. When I get the query and send it to a file I am seeing only the first 250 characters for each row instead of 10000 characters.
I will be grateful if anyone can help me suggesting a solution for this problem.
I appreciate your help in advance.
i tried this option which is not helping in my case as the max it support is 8192
Go to Tools –> Options –> Results tab. Change the “Maximum characters per column” The default is 256
jaheer,
Not sure this helps but in SQL Server 2008 the maximum number of bytes per row is 8,060. This restriction is relaxed for tables with varchar, nvarchar, varbinary, or sql_variant columns that cause the total defined table width to exceed 8,060 bytes. The lengths of each one of these columns must still fall within the limit of 8,000 bytes, but their combined widths may exceed the 8,060 byte limit in a table. For more information, see Row-Overflow Data Exceeding 8 KB in BOL
ANOTHER USE OF LEN() AND DATALENGTH() ARE :-
SELECT LEN(‘SRI ‘) — 3
SELECT DATALENGTH(‘SRI ‘) — 9
LEN() IGNORES THE SPACES BUT NOT DATALENGTH()
Wow! Sooo simple. Used it. Loves it.
pking,
Thanks for the reply, i have found the solution for the issue.it simple we need to use the below command from sqlcmd to fetch a row having column with more than 10000 characters.
sqlcmd -S 10.11.111.121 -U sa -P abc@123 -d DBNAMe -i ./conf/SQL.Archive_query.conf.sql -o ./tmp/sqlserver/SDS_Archive_Update.ddl -y 0
-y 0 –> Using this option will increase the length of a row
How can i find the length of the memo field (ntext). through sql query.
The len() function is not working for memo datatypes.
kindly suggest
Thanks
Amit
Amit, try using the command DATALENGTH().
Really nice
Thank you for having the perfect answer. I always come here first, as I have found most of what I have ever questioned answered here.
i confirm, write to target…thanks
What is the best way to set up the table structure in SQL if I do not know the maximum length of all the fields?
for varcha use max, for numbers use BIGINT, etc
thanks!
I was just wondering how to test if a column of text type is null
This helps me a lot !!
thanks
how do I get the len of a field content where fieldname is in a variable
Follwing SQL does not return the max length of the content.
ex: Declare @f1 as char(50);
set @f1=”City”
select max(len(@f1)) from
Thank you. Simple but this is the answer…
thanks you..
Hello
How can we split a column which has arabic and english text.
Thanks! Works perfectly