SQLServer: How to Read only the First x Characters from a nText Field using SQL

Often you need to display a grid with a list of text items stored as nText. Some of these items may run into thousands of characters and make the grid difficult to use so the best way is to display only a summary of the first x characters.

Instead of reading a large dataset and then using the normal .NET String functions, you can do this more efficiently if you use SQL Server to return only x characters from the nText or nvarchar(MAX) field.

Do this using the SQL SUBSTRING function.

Example of this to get the first 100 characters only:

 sql ="SELECT SUBSTRING(MyColumn, 1, 100) As SummaryText 
 FROM TableWithLongText"

SummaryText will contain 100 or fewer characters.

SUBSTRING is defined as follows:

SUBSTRING ( expression ,start , length )

Where expression can be a character, binary, text, ntext, or image.
Start is 1 for the first character (although 0 will also select the first character)
Length is the number of characters you want to return.