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, 571👍, 0💬
How to create a command button to run my macro in slide show in PowerPoint? I have created a macro t...
Where are Microsoft Teams files located on my Windows 7 system? If you have Microsoft Teams desktop ...
What contents are stored in a Web Archive (.mht) file? When you convert a word document into a singl...
How to calculate the MD5 hash of a string in PHP? You can use the following md5() function to calcul...
How to embed my Flickr picture in my Web pages? If you have your own Web site, and want imbed your F...