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, 666👍, 0💬
How to add comments on slides? I am reviewing someone else's presentation and I want to add some com...
How to download and install PdfMerger on Windows? I heard it's a nice tool split and merger PDF file...
How to use MomoCast with Safari on iPhone and iPad to cast Website and video to Chromecast on TV? If...
How to install Fitbit Bluetooth Dongle on my Windows computer? Fitbit device comes with a Bluetooth ...
What contents are stored in a Web Archive (.mht) file? When you convert a word document into a singl...