Sample SQL Server Function to Truncate a string

Below is the sample SQL Server function to Truncate a string to the given number of characters.

If the string is smaller than the given limit, it is returned as-is; otherwise, it is truncated to 3 characters less than the given limit, and '...' is placed at its end..

SQL Server Function :

create function [dbo].[TruncateString]
(
    @Str varchar(max),
    @MaxLength int
)
returns varchar(8000)
as begin
    if @MaxLength is null or @MaxLength < 3 set @MaxLength = 3
    if @MaxLength > 8000 set @MaxLength = 8000
 
return case
    when datalength(@Str) <= @MaxLength then @Str
    else left(@Str, @MaxLength - 3) + '...'
    end
end