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?

Solution 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

Solution 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

Solution 3

Are you looking for something like this? Note the CAST(C.TestimonialText AS VARCHAR(50)) in the SELECT statement.

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

Here is some test data

Test data setup

create table #t (mytext text)
insert into #t VALUES ('1234567890')
insert into #t VALUES ('123')

SELECT
  mytext,
  CAST(mytext as varchar(5)) AS Snippet
FROM #t

Results

mytext     Snippet
---------- -------
1234567890 12345
123        123

Solution 4

Basically what has happend is that you have provided invalid data type to the first parameter of the LEFT function. Make sure you cast the text data type as either varchar or nvarchar, then your query definitely works. Here is an example which I tested in the SQL Server 2005

Create table #Customer

(
 firstName varchar(30)
 ,lastName varchar(30)
 ,testimonial text
 ,testimonialDate DateTime

)

GO

INSERT INTO #Customer (firstName, lastName, testimonial, testimonialDate ) VALUES('Jonhn', 'Smith', 'we really really like your product and blaha ......', getDate())
GO
INSERT INTO #Customer (firstName, lastName, testimonial , testimonialDate) VALUES('Mary', 'Toe', 'we really really like your product and blaha ......', getDate() - 3)
GO
INSERT INTO #Customer (firstName, lastName, testimonial , testimonialDate) VALUES('Amanda', 'Palin', 'we really really like your product and blaha ......', getDate() -2 )
GO

SELECT TOP 3    C.FirstName + ' ' + C.LastName AS CustomerName    ,LEFT( CAST(C.Testimonial as varchar(50)),50) AS TestimonialSnippet    ,C.TestimonialDate FROM #Customer AS C  ORDER BY C.TestimonialDate DESC
GO
Drop table #Customer
GO

View more solutions

Comments

[p.campbell]

Thanks Bird. SUBSTRING is the way to go if you don't want to cast to a varchar.

[p.campbell]

Thanks Sergey, appreciate the note about the depricated datatype.