SQL Server fnGEN_WrapText Function: Wrap Long Text into Multiple Lines Using T-SQL

Introduction

In SQL Server, sometimes we need to display long text in multiple lines instead of showing everything in a single continuous line.

For example, suppose you have a long product description, customer address, report text, or notification message. If you want to restrict each line to a maximum number of characters, you can create a custom SQL Server function.

In this tutorial, we will create a user-defined function named dbo.fnGEN_WrapText that wraps long text based on a specified maximum length.

The function also supports a custom delimiter, allowing you to separate wrapped lines using characters such as |, -, or any other separator.

1. What Does fnGEN_WrapText Do?

The fnGEN_WrapText function divides long text into smaller lines based on the specified maximum length.

It follows two important rules:

1. It tries to break the text at the last available space before the maximum character limit.

2. If no space is available, it breaks the text exactly at the maximum character limit.

Example

Suppose we have the following text:

SQL Server is a powerful relational database management system.

If we set the maximum length to 15 and delimiter to |, the output will look like:

SQL Server is a|powerful|relational|database|management|system.

This makes long text easier to display in reports and other applications.

2. Complete SQL Server Function

First, let's understand the complete function.

IF NOT EXISTS
(
    SELECT 1
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'dbo.fnGEN_WrapText')
      AND type = N'FN'
)
EXEC
(
    N'CREATE FUNCTION dbo.fnGEN_WrapText()
      RETURNS NVARCHAR(MAX)
      AS
      BEGIN
          RETURN NULL
      END'
)
GO

ALTER FUNCTION dbo.fnGEN_WrapText
(
    @Text      NVARCHAR(MAX),
    @Delimiter NVARCHAR(10),
    @MaxLen    INT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN

    IF @Text IS NULL OR @MaxLen IS NULL OR @MaxLen < 1
        RETURN @Text

    DECLARE
        @Rest   NVARCHAR(MAX) = LTRIM(RTRIM(@Text)),
        @Result NVARCHAR(MAX) = N'',
        @Space  INT

    SET @Delimiter = ISNULL(@Delimiter, N'|')

    WHILE LEN(@Rest) > @MaxLen
    BEGIN

        SET @Space = CHARINDEX
        (
            N' ',
            REVERSE(LEFT(@Rest, @MaxLen + 1))
        )

        IF @Space = 0
        BEGIN

            SET @Result = @Result
                        + LEFT(@Rest, @MaxLen)
                        + @Delimiter

            SET @Rest = LTRIM
            (
                SUBSTRING(@Rest, @MaxLen + 1, LEN(@Rest))
            )

        END
        ELSE
        BEGIN

            SET @Space = @MaxLen + 2 - @Space

            SET @Result = @Result
                        + RTRIM(LEFT(@Rest, @Space - 1))
                        + @Delimiter

            SET @Rest = LTRIM
            (
                SUBSTRING(@Rest, @Space + 1, LEN(@Rest))
            )

        END

    END

    RETURN @Result + @Rest

END
GO

3. Understanding the Function Step by Step

Step 1: Check Whether the Function Already Exists

IF NOT EXISTS
(
    SELECT 1
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'dbo.fnGEN_WrapText')
      AND type = N'FN'
)

Here, we check whether the function already exists in the current database.

• sys.objects: Contains information about database objects.

• OBJECT_ID(): Returns the object ID of a database object.

• type = 'FN': Identifies a SQL scalar-valued function.

If the function does not exist, SQL Server executes the CREATE FUNCTION statement.

Step 2: Create a Temporary Function Definition

EXEC
(
    N'CREATE FUNCTION dbo.fnGEN_WrapText()
      RETURNS NVARCHAR(MAX)
      AS
      BEGIN
          RETURN NULL
      END'
)

SQL Server requires an object to exist before we can alter it.

Therefore, the script first creates a basic placeholder function and then modifies it using ALTER FUNCTION.

This approach is useful when deploying database scripts across multiple environments.

Step 3: Define Function Parameters

ALTER FUNCTION dbo.fnGEN_WrapText
(
    @Text      NVARCHAR(MAX),
    @Delimiter NVARCHAR(10),
    @MaxLen    INT
)

The function accepts three parameters:

Step 4: Validate Input Parameters

IF @Text IS NULL OR @MaxLen IS NULL OR @MaxLen < 1
    RETURN @Text

Before processing the text, the function checks whether the input is valid.

If the input text is NULL or the maximum length is invalid, the function returns the original input.

Step 5: Initialize Variables

DECLARE
    @Rest   NVARCHAR(MAX) = LTRIM(RTRIM(@Text)),
    @Result NVARCHAR(MAX) = N'',
    @Space  INT

These variables are used during text processing.

• @Rest: Stores the remaining text that still needs to be processed.

• @Result: Stores the final wrapped text.

• @Space: Stores the position of the last available space.

LTRIM() and RTRIM() remove leading and trailing spaces.

Step 6: Set the Default Delimiter

SET @Delimiter = ISNULL(@Delimiter, N'|')

If the user does not provide a delimiter or passes NULL, the function uses | as the default separator.

For example:

SELECT ISNULL(NULL, '|') AS Delimiter;

Output:

|

Step 7: Process Text Using WHILE Loop

WHILE LEN(@Rest) > @MaxLen
BEGIN

The loop continues as long as the remaining text is longer than the specified maximum length.

For example, if the text contains 50 characters and the maximum length is 15, the function repeatedly processes the text until the remaining portion is within the limit.

Step 8: Find the Last Space

SET @Space = CHARINDEX
(
    N' ',
    REVERSE(LEFT(@Rest, @MaxLen + 1))
)

This is the most important part of the function.

Let's understand it:

• LEFT(): Extracts the first specified number of characters.

• REVERSE(): Reverses the extracted text.

• CHARINDEX(): Finds the first space in the reversed text.

Why do we use REVERSE()?

Because we want to find the last space in the original text before the maximum character limit.

Reversing the text allows us to find that last space using CHARINDEX().

Step 9: Handle Text Without Spaces

IF @Space = 0
BEGIN
    SET @Result = @Result
                + LEFT(@Rest, @MaxLen)
                + @Delimiter

    SET @Rest = LTRIM
    (
        SUBSTRING(@Rest, @MaxLen + 1, LEN(@Rest))
    )
END

If no space is found, the function breaks the text exactly at the maximum character limit.

For example:

ABCDEFGHIJKLMNOPQRSTUVWXYZ

Maximum length: 10

Output:

ABCDEFGHIJ|KLMNOPQRST|UVWXYZ

Step 10: Handle Text With Spaces

ELSE
BEGIN
    SET @Space = @MaxLen + 2 - @Space

    SET @Result = @Result
                + RTRIM(LEFT(@Rest, @Space - 1))
                + @Delimiter

    SET @Rest = LTRIM
    (
        SUBSTRING(@Rest, @Space + 1, LEN(@Rest))
    )
END

If a space is found, the function breaks the text at that space instead of splitting a word.

This makes the output more readable.

For example:

SQL Server Database Development

Maximum length: 15

The function prefers breaking at a word boundary rather than splitting a word.

Step 11: Return Final Result

RETURN @Result + @Rest

Once all the text has been processed, the function combines the already wrapped text with the remaining text and returns the final output.

4. How to Execute the Function

After creating the function, use the following query:

SELECT dbo.fnGEN_WrapText
(
    N'SQL Server is a powerful relational database management system.',
    N'|',
    15
) AS WrappedText;

Example output:

SQL Server is a|powerful|relational|database|management|system.

5. Test With Different Delimiters

You can use different delimiters according to your requirements.

Using Pipe (|)

SELECT dbo.fnGEN_WrapText
(
    N'Learn SQL Server programming step by step',
    N'|',
    10
);

Using Hyphen (-)

SELECT dbo.fnGEN_WrapText
(
    N'Learn SQL Server programming step by step',
    N'-',
    10
);

Using Comma (,)

SELECT dbo.fnGEN_WrapText
(
    N'Learn SQL Server programming step by step',
    N',',
    10
);

Using Default Delimiter

SELECT dbo.fnGEN_WrapText
(
    N'Learn SQL Server programming step by step',
    NULL,
    10
);

When the delimiter is NULL, the function uses |.

6. Real-World Use Cases

This function can be useful in several situations:

1. SSRS Reports: Display long descriptions in multiple lines.

2. Text Formatting: Break long messages into smaller sections.

3. Data Export: Format text before exporting data to external applications.

4. Notification Systems: Wrap long notification messages.

5. Database Applications: Format customer addresses and product descriptions.