SQL Server Truncating Message with '...'
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.
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, 912👍, 0💬
What contents are stored in a Web Archive (.mht) file? When you convert a word document into a singl...
How to download and install SoapUI on Windows systems? Here are the steps to download and install So...
How to view the document.xml file with a Web browser? Since the document.xml file contains text cont...
What browser to use to cast Website and video to Chromecast devices on my Android phone? On your And...
How to add more rows to a table in Microsoft Word documents? One quick way to add rows to a table is...