T-SQL:从 text 或 ntext 列中选择前 n 个字符

2021-09-10 00:00:00 tsql sql-server

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

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.

考虑这个 T-SQL 的精简片段:

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

这会产生一个错误:

参数数据类型文本无效左函数的参数 1.

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

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

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

推荐答案

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

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

相关文章