SQL Server: split long text into multiple lines with max # of characters per line

Question

Is there a function in SQL Server that splits long text into multiple lines?

Say I have 1,000 characters and need to split the text into multiple lines with max of 80 characters per line, but you can only split at spaces not in the middle of a word?

Thanks.

http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco — gloomy.penguin, Sep 03 '13 at 22:35

score 1 · Answer 1 · answered Sep 04 '13 at 02:19

You can split the text using recursive CTEs. Here is an example:

with t as (
      select 1 as id, 'abcefghijkl' as line union
      select 2, 'zyx'
     ),
     const as (
      select 1 as linelen
     ),
     splitlines as (
      select id, left(line, linelen) as part, substring(line, linelen + 1, len(line)) as rest
      from t cross join const
      union all
      select id, left(rest, linelen) as part, substring(rest, linelen + 1, len(rest))
      from splitlines cross join const
      where len(rest) > 0
    )
select *
from splitlines;

Your question is unclear as to whether you want to split a single variable or a column in a table. In any case, the values go in the t alias. The line length goes in the const alias.

Yes, the values are in a single column in a table. I need to break down the text into multiple lines/rows as multiple records inserted into another table. — 3firelegs, Sep 06 '13 at 03:35
@3firelegs . . . Then a recursive CTE should do what you need. — Gordon Linoff, Sep 06 '13 at 10:55

Hart CO · Answer 2 · 2013-09-03T22:54:13.777

There's not a built in split function, but you can use multiple substrings/charindex, something like:

DECLARE @FieldName VARCHAR(MAX) = 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industrys standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.'
SELECT  SUBSTRING(@FieldName,1,CHARINDEX(' ',@FieldName,70)-1)
       ,SUBSTRING(@FieldName,CHARINDEX(' ',@FieldName,70)+1,CHARINDEX(' ',@FieldName,140)-CHARINDEX(' ',@FieldName,70))
       ,SUBSTRING(@FieldName,CHARINDEX(' ',@FieldName,140)+1,CHARINDEX(' ',@FieldName,210)-CHARINDEX(' ',@FieldName,140))

Demo: SQL Fiddle

Or search for/create a UDF, seem to be plenty out there.

Could use the substring/charindex method in a cte and pivot the results too.

SQL Server: split long text into multiple lines with max # of characters per line

2 Answers2