Truncate the middle of a long string

This is a little tip you might find handy.

Every typical database stores important strings like place names that are then displayed in reports and charts. Most of the time these strings are relatively short (e.g. 30-40 characters) and the reports and charts look fine, but occasionally some records happen to require a much longer string (e.g. 500+ characters) and these strings might cause the reports and charts to become a bit less visually pleasing. In some cases you can get rendering errors where the title or some attribute in a chart is just too long.

Most of the time, the most important information in such a long name is at the start and/or the end of the string; to compromise and show as much as we can fit on the page, we can truncate the middle of the string and replace it with “..” to show that some text has been removed.

The simplest way to do this without a complex expression involving some combination of LENGTH, CASE and SUBSTR is with a single REGEXP_REPLACE using backreferences to retain the start and end of the string:

select REGEXP_REPLACE(
    'My very very very long string abcdefghijklmnopqrstuvwxyz hello world wow this string is really very very very long.',
    '^(.{50}).{3,}(.{50})$', -- get the first and last 50 characters
    '\1..\2',                -- replace the middle with ".."
    1, 1, 'n'                -- treat entire string including newlines
) from dual;

I’ve chosen to keep the first 50 and the last 50 characters in this example. The result:

My very very very long string abcdefghijklmnopqrst..rld wow this string is really very very very long.

If you want to use this trick, you can adjust the length of the resulting string to whatever maximum length you want: choose how many characters to take from the start of the string and the end of the string by changing the two numbers (e.g. 50) in the regular expression.

Testing with a shorter string, we expect it to return the string unmodified:

select REGEXP_REPLACE(
    'This is a nice shorter string with <= 102 characters; the entire string should be returned unmodified.',
    '^(.{50}).{3,}(.{50})$', '\1..\2', 1, 1, 'n'
) from dual;

This is a nice shorter string with <= 102 characters; the entire string should be returned unmodified.

If the incoming string is already 102 characters or shorter, no replacement is done. In every case, we have guaranteed the resulting string will never be longer than 102 characters.