Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

How to get length of Text, NText and Image columns in SQL Server

MSSQLTips author Greg Robidoux By:   |   Read Comments (4)   |   Related Tips: More > Data Types

Problem
There is sometimes a need to figure out the maximum space that is being used by a particular column in your database.  You would initially think that the LEN() function would allow you to do this, but this function does not work on Text, NText or Image data types, so how do you figure out the length of a value in a column that has one of these data types?

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.

Here is an example of running these commands on an IMAGE data type using the LEN function:

SELECT name, LEN(packagedata)
FROM
dbo.sysdtspackages

query results

 

Here is an example of running these commands on an IMAGE data type using the DATALENGTH function:

SELECT name, DATALENGTH(packagedata)
FROM
dbo.sysdtspackages

query results

 

If you wanted to find out the maximum length used for all of your records you could issue a command such as the following:

SELECT TOP 1 name, DATALENGTH(packagedata)
FROM
dbo.sysdtspackages
ORDER BY
2 DESC

 

That is all there is to it.  Another little function that you probably won't use often, but this is helpful to know it is there when you do need to use it.

Next Steps



Last Update: 2/27/2007


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Wednesday, December 26, 2012 - 2:24:33 PM - Rodney Read The Tip

It is important to note that DATALENGTH returns the number of BYTES used to store the string INCLUDING any ending whitespace, while the LEN function returns the number of CHARACTERS in a string EXCLUDING any ending whitespace.

For example, if you insert the value 'TEST' into an NVARCHAR field, the LEN function returns 4 while the DATALENGTH function returns 8 (each character in an NVARCHAR field requires two bytes of storage).  If you instead inserted the value 'TEST   ' (with three spaces at the end of the string) the LEN function would still return 4 while the DATALENGTH function returns 14 (8 bytes for TEST plus 6 bytes for the three trailing spaces).  Granted you still can't use the LEN function on large data types, but you should keep this difference in mind if you are planning to use the value in any string manipulations or character space calculations.


Friday, June 29, 2012 - 1:09:05 PM - John Read The Tip

Greg,

 

Nice tip. Helped me out :-)

 

John


Friday, April 11, 2008 - 9:55:00 AM - admin Read The Tip

Shaileshk,

Thank you.  That is certainly one approach.

Thank you,
The MSSQLTips.com Team


Friday, April 11, 2008 - 1:43:11 AM - shaileshk Read The Tip

if you want to remove duplicate records with ntext,text and image datatype .please do the step as given into the link

http://www.codegroups.com/blog/index.php/deleting-duplicate-records-from-table-with-text-ntext-or-image-data-type/

I hope this is help !

Regards,
Shaileshk

 




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.