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 (http://blog.SQLAuthority.com)

About these ads

78 thoughts on “SQL SERVER – Find Length of Text Field

  1. Hi, It is really useful.

    I have the database – in which i have to generate script for 10 tables only – as i dont have Enterprise Manager, i want to generate the script through Query Analyser onl.

    ie the script should like the result of –Enterprise Manager—> Select the Table(s)–Rt Click–All Tasks — Generate SQL Script.

    How can i get this?

    Thanks in Advance.

    Regards — Poov

  2. Thank you! Before this I found a bunch of other SQL’s _languages_ that accept just LENGTH, but THIS is what I really needed.

    Thank you!

  3. @Antonio,

    You can use this stord procedure

    SP_HELP table_name

    it will list out all the fields, their sizes, constraints, indexes….

    It gives very good information.

    Hope this helps.

    Thanks,
    Imran.

  4. i want to store more than 8000 chars in table field..
    i hv used varchar(max) , text(max) ,but i m not able to get success in it..

    everytime data gets truncated whn i insert it..

    so can u tell me how to overcome from this problem..?

  5. I am having the same issue as Ramani. Any solution for this ?
    I have a text field and what ever number of characters that I save on that field, it returns only 1001 characters. But when I checked with DATALENGTH() function it shows that it is having the same number of characters that I entered.

  6. @Chaminda,

    Open SQL Server Management Studio
    Go to Tools – Options (Tools exists at the top left of SSMS)
    Expand Query Results
    Expand SQL Server
    Click on Results to Grid

    On right side, Set Value for :
    Maximum Characters retrieved : Set a higher value.

    Because of this, SQL Server is returning fewer characters. Change this value to a higher value and see the difference

    ~ IM.

  7. Hello!

    But, when the field is null ?
    how can i know the size of a field, have or not have data in the table ??

    Thanks!

  8. can anybody help me in using length function
    how i can make any field have 4 digits (e.g 7456)
    to 6 digits(i.e 007456) in my data base using sql query so that every field with length 4 is changed to length 6
    but zero should come in front

  9. Please help.
    Need to add/combine 3 columns

    Column1 column column3
    eddie lewis 125.87456

    query is select :

    column1+column2+cast(column3/100 as varchar(20))

    returns – eddielewis1.25874 i.e missing last 2 digits of column3 …… please help

    Thanks

    • Hey Andazi,
      Change the query to

      column1+column2+cast(column3/100 as varchar(50))

      As the cast is only getting the first 20 characters

  10. You guys explained how to change the data amount being returned directly in SQL server, but what about the amount being returned in an ASP page?

  11. This is great!

    But how can I find out the MAXSIZE of the field in question?

    We have recently updated our DB to increase the size of certain fields. I need to check if the user updated their DB before performing any work on those fields.

  12. Hello,

    I have a table which has field1, field2 and field3 columns of type int, the combination of those columns represent 10 digit phone number. I know field3 always have 4 digits, but MS SQL trims the right most 0s. all i am tring to acheive is to provide the user with one field 10 digit number.

    here is my query:

    INSERT INTO [Info Direct Res new]
    SELECT ‘Info Direct’, /* Datbase Name */
    DECLARE @MAX_FIELD_LEN int;
    DECLARE @fieldLength int;
    SET @MAX_FIELD_LEN=4;
    SET @fieldLength = len(Cast(field3 as varchar));
    cast(field1 as varchar)+cast(field2 as varchar)+RIGHT(’0′,@MAX_FIELD_LEN-@fieldLength)+Cast(field3 as varchar), /* Phone Number */
    field5, /*First name*/
    field6, /*Last Name*/
    ISNULL(field8,”) + ‘ ‘ + ISNULL(field9,”)+ ‘ ‘ + ISNULL(field10,”)+ ‘ ‘ + ISNULL(field11,”)+ ‘ ‘ + ISNULL(field12,”), /* Full Address */
    field13, /* city */
    field14, /* province */
    field15, /* postal code */
    ‘ ‘
    from stcir1001

    I get syntax error where i say declare

    thanks,

  13. I have a tabe in oracle which has a varchar field with length of 48. I need to select records that are not in the length of either 8, 15 & 16 size.
    The second criteria is to identify records which are not numeric i.e. any character value should be displayed.

  14. The msdn website shows that len and datalength functions are different.

    LEN- Returns the number of characters of the specified string expression, excluding trailing blanks.

    DATALENGTH – Returns the number of bytes used to represent any expression.

  15. 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?

  16. 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

  17. 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

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

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

    LEN() IGNORES THE SPACES BUT NOT DATALENGTH()

  19. 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

  20. 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

  21. 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.

  22. 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?

  23. 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

  24. Pingback: SQL SERVER – Weekly Series – Memory Lane – #034 | Journey to SQL Authority with Pinal Dave

  25. I thinks Datalength return number of bytes not the number of characters. If you have only spaces in the text field then how to check that field is empty.

  26. Awesome! Looking forward for such a feature to be made available in my favorite programming language. I’ve been dying to us “length” for short arrays and “datalength” for long arrays.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s