Today, I had a need to write a function that elegantly truncated a string to a given maximum length, but give the end user a contextual clue that some of the string has been removed. Generally this is done with ellipsis (…), but sometimes other clues can be given.
Here’s an English example of what I mean.
Original message :
At the zoo today we saw lions, tigers, elephants, a rhinoceroses and hippopotamuses.
Truncate string to 50 characters with contextual clue of omission :
At the zoo today we saw lions, tigers, elephants,…
Notice the “…” is a marker that signifies there is an omission, so it acts as an overflow marker. The overflow marker only comes into play if it exceeds the maximum allowed length.
Anyway, here’s what I came up with. To cover both character semantics (length and byte), I’ve included a character version and a byte version.
create or replace function StrMaxLength(pStr varchar2, pMaxLength pls_integer, pOverflowMarker varchar2 default '…') return varchar2 is
-- Truncates input string to max length of chars, using Overflow Marker as an indicator of omission if truncation occurred
begin
if pMaxLength < 0 then
raise_application_error(-20001, 'Max Length must be 0 or greater');
end if;
return case
when pStr is null or coalesce(pMaxLength, 0) = 0 then null
when length(pStr) > pMaxLength then substr(pStr, 1, greatest(pMaxLength - coalesce(length(pOverflowMarker), 0), 0)) || substr(pOverflowMarker, 1, pMaxLength)
else pStr
end;
end;
/
create or replace function StrMaxLengthB(pStr varchar2, pMaxLength pls_integer, pOverflowMarker varchar2 default '…') return varchar2 is
-- Truncates input string to max length of bytes, using Overflow Marker as an indicator of omission if truncation occurred
begin
if pMaxLength < 0 then
raise_application_error(-20001, 'Max Length must be 0 or greater');
end if;
return case
when pStr is null or coalesce(pMaxLength, 0) = 0 then null
when lengthB(pStr) > pMaxLength then substrb(pStr, 1, greatest(pMaxLength - coalesce(lengthb(pOverflowMarker), 0), 0)) || substrb(pOverflowMarker, 1, pMaxLength)
else pStr
end;
end;
/
Notes :
The greatest(…) bit ensures length is not negative, in cases where the overflow marker is longer than the maximum allowed length.
The reason I substr(pOverflowMarker…, …) to max length it to ensure the overflow marker itself doesn’t cause the output string to exceed the allowed length.
Here’s an example of its usage :
SQL> with data(str) as (
2 select 'The quick brown fox jumped over a lazy dog' from dual
3 )
4 select *
5 from (
6 select level - 1 MaxLength,
7 StrMaxLength(str, level - 1) str_def_overlow,
8 StrMaxLength(str, level - 1, null) str_no_overlow,
9 StrMaxLength(str, level - 1, ' (more >>)') str_more_overlow
10 from data
11 connect by level <= LengthB(str) + 1
12 )
13 order by MaxLength desc;
MAXLENGTH STR_DEF_OVERLOW STR_NO_OVERLOW STR_MORE_OVERLOW
---------- -------------------------------------------- -------------------------------------------- --------------------------------------------
42 The quick brown fox jumped over a lazy dog The quick brown fox jumped over a lazy dog The quick brown fox jumped over a lazy dog
41 The quick brown fox jumped over a lazy… The quick brown fox jumped over a lazy do The quick brown fox jumped over (more >>)
40 The quick brown fox jumped over a laz… The quick brown fox jumped over a lazy d The quick brown fox jumped ove (more >>)
39 The quick brown fox jumped over a la… The quick brown fox jumped over a lazy The quick brown fox jumped ov (more >>)
38 The quick brown fox jumped over a l… The quick brown fox jumped over a lazy The quick brown fox jumped o (more >>)
37 The quick brown fox jumped over a … The quick brown fox jumped over a laz The quick brown fox jumped (more >>)
36 The quick brown fox jumped over a… The quick brown fox jumped over a la The quick brown fox jumped (more >>)
35 The quick brown fox jumped over … The quick brown fox jumped over a l The quick brown fox jumpe (more >>)
34 The quick brown fox jumped over… The quick brown fox jumped over a The quick brown fox jump (more >>)
33 The quick brown fox jumped ove… The quick brown fox jumped over a The quick brown fox jum (more >>)
32 The quick brown fox jumped ov… The quick brown fox jumped over The quick brown fox ju (more >>)
31 The quick brown fox jumped o… The quick brown fox jumped over The quick brown fox j (more >>)
30 The quick brown fox jumped … The quick brown fox jumped ove The quick brown fox (more >>)
29 The quick brown fox jumped… The quick brown fox jumped ov The quick brown fox (more >>)
28 The quick brown fox jumpe… The quick brown fox jumped o The quick brown fo (more >>)
27 The quick brown fox jump… The quick brown fox jumped The quick brown f (more >>)
26 The quick brown fox jum… The quick brown fox jumped The quick brown (more >>)
25 The quick brown fox ju… The quick brown fox jumpe The quick brown (more >>)
24 The quick brown fox j… The quick brown fox jump The quick brow (more >>)
23 The quick brown fox … The quick brown fox jum The quick bro (more >>)
22 The quick brown fox… The quick brown fox ju The quick br (more >>)
21 The quick brown fo… The quick brown fox j The quick b (more >>)
20 The quick brown f… The quick brown fox The quick (more >>)
19 The quick brown … The quick brown fox The quick (more >>)
18 The quick brown… The quick brown fo The quic (more >>)
17 The quick brow… The quick brown f The qui (more >>)
16 The quick bro… The quick brown The qu (more >>)
15 The quick br… The quick brown The q (more >>)
14 The quick b… The quick brow The (more >>)
13 The quick … The quick bro The (more >>)
12 The quick… The quick br Th (more >>)
11 The quic… The quick b T (more >>)
10 The qui… The quick (more >>)
9 The qu… The quick (more >>
8 The q… The quic (more >
7 The … The qui (more
6 The… The qu (more
5 Th… The q (mor
4 T… The (mo
3 … The (m
2 Th (
1 T
0
43 rows selected.
Paulzip's Oracle Blog 