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

By: Greg Robidoux | Read Comments (2) | Print Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com. Related Tips: More

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

• Add this command to your SQL Server T-SQL toolbox
• Take a look at these other useful T-SQL functions
  • SQL Server 2000 Text Data Manipulation
  • Date/Time Conversions Using SQL Server
  • Retrieving SQL Server Index Properties with INDEXPROPERTY
  • Retrieving SQL Server Database Properties with DATABASEPROPERTYEX

Share:

Share

Tweet

Connect with MSSQLTips.com Follow us on twitter... Let's connect on facebook! Join us on Google+

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code