First create the Function below. Next, run the query against your source DB, be sure to change the parameters so you get the length you want. This was used on a migration from MAS200 to MAX where the Sales Order Notes was a long string in an old text column (this was a challenge too) and MAX specific width.
/* Run this query once you’ve created the function */
select t.SalesOrderNo, LineSeqNo, l.LineNumber, l.Line
from MAS_SO_Notes t
cross apply SplitLines(CommentText, 50, ‘ ‘) l
where SalesOrderNo = ‘0065047’
order by SalesOrderNo, LineSeqNo
**** Function ****
USE [ExactMAXSAM]
GO
/****** Object: UserDefinedFunction [dbo].[SplitLines] Script Date: 05/08/2015 20:57:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
–select SalesOrderNo, CommentText , LEN(CommentText) as L
–from MAS_SO_Notes
–where SalesOrderNo = ‘0065047’
CREATE FUNCTION [dbo].[SplitLines]
(
@pString VARCHAR(7999),
@pLineLen INT,
@pDelim CHAR(1)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
WITH
E1(N) AS ( –=== Create Ten 1’s
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 –10
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), –100
E4(N) AS (SELECT 1 FROM E2 a, E2 b), –10,000
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E4),
lines AS (
SELECT TOP 1
1 as LineNumber,
ltrim(rtrim(SUBSTRING(@pString, 0, N))) as Line,
N + 1 as running_len
FROM cteTally
WHERE N <= DATALENGTH(@pString)+1
AND N <= @pLineLen
AND SUBSTRING(@pString, N, 1) = ‘ ‘
ORDER BY N DESC
UNION ALL
SELECT LineNumber, Line, running_len
FROM (
SELECT LineNumber + 1 as LineNumber,
ltrim(rtrim(SUBSTRING(@pString, running_len, N))) as Line,
running_len + N as running_len,
ROW_NUMBER() OVER (ORDER BY N DESC) as r
FROM cteTally, lines
WHERE N <= DATALENGTH(@pString)+1 – running_len
AND N < @pLineLen
AND SUBSTRING(@pString + @pDelim, running_len + N, 1) = ‘ ‘
) A
WHERE r = 1
)
SELECT LineNumber, Line
FROM lines
/*
select t.SalesOrderNo, LineSeqNo, l.LineNumber, l.Line
from MAS_SO_Notes t
cross apply SplitLines(CommentText, 50, ‘ ‘) l
where SalesOrderNo = ‘0065047’
order by SalesOrderNo, LineSeqNo
*/