T-SQL: Selecting top n characters from a text or ntext column

Question

Consider a scenario where you'd like to pull the last x entries from a table. The column we want contains testimonials about a product. For performance reasons, we only want to grab the first 50 characters from the testimonial. The column is named TestimonialText and is of type text.

Consider this condensed snippet of T-SQL:

SELECT TOP 10
    C.FirstName + ' ' + C.LastName AS CustomerName
    ,LEFT(C.TestimonialText,50) AS TestimonialSnippet
    ,C.TestimonialDate

FROM Customer AS C  
ORDER BY C.TestimonialDate DESC

This produces an error:

Argument data type text is invalid for argument 1 of left function.

Question: how to extract just the first few n characters of the text or ntext column?

Answer 1

I think SUBSTRING would be a better choice. Try this:

SELECT TOP 10
    C.FirstName + ' ' + C.LastName AS CustomerName
    ,SUBSTRING(C.TestimonialText,1,50) AS TestimonialSnippet
    ,C.TestimonialDate
FROM Customer AS C  
ORDER BY SUBSTRING(C.TestimonialText,1,50) DESC

Answer 2

If you use SQL Server 2005 or above, do not use text datatype, because it's depricated. Use varchar(max) or nvarchar(max). All string functions will work. Read more here: http://msdn.microsoft.com/en-us/library/ms178158.aspx