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

Question

考虑这样一个场景:您想要从表中提取最后 x 个条目。我们想要的专栏包含有关产品的推荐。出于性能原因，我们只想获取推荐中的前 50 个字符。该列名为 TestimonialText，类型为 text。

考虑这个 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

这会产生一个错误:

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

问题:如何提取 text 或 ntext 列的前几个 n 个字符？

Answer 1

我认为 SUBSTRING 会是更好的选择。试试这个:

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