Oracle Truncating Message with '...'


What is the best way in Oracle 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


Basically, you are asking the logic to be implemented in an Oracle query,

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

A straightforward solution is the following:

SELECT CASE WHEN LENGTH(input)>140 THEN SUBSTR(input,1,140)||'...'
   ELSE input END 

But using the REGEXP_REPLACE() function will give you a better solution:

SELECT REGEXP_REPLACE(input,'(.{140}).*','\1...',1,0,'n') FROM DUAL;

2015-09-16, 519👍, 0💬