Click on the banner to learn about and purchase my database training on Azure

SQL Server - Table-valued function to break a string into lines up to N characters long

Views: 1.756 views
This post is the 2 part of 5 in the series. string Split
Reading Time: 3 minutes

Hello people,
All right with you ?

In this post today, I will share with you a Table-valued UDF function that lets you break strings into lines, forcing the maximum length of each line to be N characters separated by a separator character defined in the function call.

This function came from a need in a critical project where I work, where we have VARCHAR (MAX) strings and we need to export these strings into a TXT file with a maximum length of 60 characters, keeping an Id to identify the original record and a ranking (I used ROW_NUMBER) to identify the order of each part of the string.

Interested in learning more about split?

Examples of use

Simple Example:

Example with CROSS APPLY:


Function source code

To use the function shown above, simply create the table-valued CLR function in your instance. To better understand what the CLR is and how to create your first CLR library, see more in the post. Introduction to SQL Common Language Runtime (CLR) in SQL Server.

That's it folks!
A hug and see you next.