SQL Server Truncating Message with '...'

Q

What is the best way in SQL Server to truncate a string to 140 characters and add "..." to the end if there are more than 140 characters in the string? I need this to display initial parts of user comments on my home page.

✍: FYIcenter

A

Basically, you are asking the logic to be implemented in a SQL Server query,

If the string has more than 140 characters, 
   Return the first 140 characters with "..." added to the end
Else
   Return the string as is

A straightforward solution is the following:

SELECT CASE 
   WHEN LEN(input)>140 THEN SUBSTRING(input,1,140)+'...'
   ELSE input
   END;

But the following solution should have a better performance:

SELECT SUBSTRING(input,1,140) + REPLACE(REPLACE(CAST(
   PATINDEX('%_%',SUBSTRING(input,140,999999)) 
   AS NVARCHAR(MAX)),'0',''),'1','...');   

Of course, when SQL Server supports Regular Expression, we will have a much better solution.

2015-09-16, 666👍, 0💬