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, 845👍, 0💬
Where to find answers to frequently asked questions on Microsoft Word? I want to know how to open PD...
Where to find answers to frequently asked questions on Microsoft Outlook? I want to know how to know...
Why I am getting the "run or save ChromeSetup.exe" pop up box when trying to install Google Chrome 3...
How to save word documents into Unicode UTF-8 text files? When I convert Word documents into text fi...
How to install Mozilla Firefox 2.0 add-on: FireFTP? FireFTP is a Mozilla Firefox 2 add-on that provi...