SQL SERVER – Find Length of Text Field

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)

,
Previous Post
SQLAuthority.com News – Journey to SQL Authority Milestone of SQL Server
Next Post
SQL SERVER – Retrieve Current DateTime in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}

Related Posts

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?

    Reply
    • Why do you need this? If you want to view full content, export to text file and view from there

      Reply
      • 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

    Reply
  • Thank you!

    Reply
  • Hello All

    I want to fetch only 100 character from spacific column, plz tell me what is query for that.

    Reply
  • 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

    Reply
  • 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

    Reply
  • Srikanth Nallamothu
    June 17, 2011 4:36 pm

    ANOTHER USE OF LEN() AND DATALENGTH() ARE :-

    SELECT LEN(‘SRI ‘) — 3
    SELECT DATALENGTH(‘SRI ‘) — 9

    LEN() IGNORES THE SPACES BUT NOT DATALENGTH()

    Reply
  • Wow! Sooo simple. Used it. Loves it.

    Reply
  • 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

    Reply
  • 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

    Reply
  • Really nice

    Reply
  • 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.

    Reply
  • i confirm, write to target…thanks

    Reply
  • 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?

    Reply
  • I was just wondering how to test if a column of text type is null
    This helps me a lot !!
    thanks

    Reply
  • 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

    Reply
  • Thank you. Simple but this is the answer…

    Reply
  • thanks you..

    Reply
  • Hello

    How can we split a column which has arabic and english text.

    Reply
  • Thanks! Works perfectly

    Reply

Leave a Reply

Menu