SQL String length limit but return multiple rows

Question

I have a table called notes with a column that is nvarchar(MAX), this columns may have 10 characters or it may have 800. I need to slice this up into 250 character segments.

If this line below was the value in the table I need to create 2 rows with a max of 250 characters. If you count characters you would split the word voicemail. I need to see it is in the middle of a word and not break it up.

Blanket Lien Filed, account returned to Provider. Blah Blah will continue to follow and pursue lien payment. Called Pt Blahat xxx-xxx-xxxx. Left voice mail. Voicemail greeting did not state a name. Called Pt at xxx-xxx-xxxx. Left voice mail. Voicemail greeting did not state a name.

I need it to look something like this and I have no idea to do this.

Row 1:

Blanket Lien Filed, account returned to Provider. Blah Blah will continue to follow and pursue lien payment. Called Pt Blahat xxx-xxx-xxxx. Left voice mail.

Row 2:

Voicemail greeting did not state a name. Called Pt at xxx-xxx-xxxx. Left voice mail. Voicemail greeting did not state a name.

I have tried this but it cuts the words off.

SELECT     Acct, SUBSTRING(Notes, 1, 249) as Note, 'A1' AS  Prefix
FROM         dbo.[RegionalOneNotesResults]
UNION
SELECT     Acct, SUBSTRING(Notes, 250, 249) as Note, 'B2' AS Prefix
FROM          dbo.[RegionalOneNotesResults]

Any help would be appreciated

This should do it: http://stackoverflow.com/questions/19836290/wrap-text-in-sql-server-using-function — Greg the Incredulous, Nov 06 '14 at 02:50

score 0 · Answer 1 · answered Nov 06 '14 at 02:53

This code should do what you want:

    CREATE PROC WORD_WRAPPED_STRING
          @string VARCHAR(800),
          @length INT

    AS

 SET NOCOUNT ON

 DECLARE  @string_length INT,
          @output        VARCHAR(800),
          @trailing_char CHAR(1),
          @leading_char  CHAR(1)

 IF @length = LEN(@string)
   BEGIN
     PRINT 'please provide a smaller length for dividing the string.'

     RETURN
   END

 IF CHARINDEX(CHAR(13),@string) <> 0
   BEGIN
     SELECT @string = REPLACE(@string,CHAR(10),' ')

     SELECT @string = REPLACE(@String,CHAR(13),)
   END

 SET CONCAT_NULL_YIELDS_NULL OFF

 SELECT @string_length = LEN(@string)

 WHILE @string_length & GT
   ; @length

 BEGIN
   SET @trailing_char = SUBSTRING(LEFT(@string,(@length + 1)),(@length + 1),
                                  1)

   SET @leading_char = SUBSTRING(LEFT(@string,@length),@length,1)

   IF @leading_char = ' '
       OR @trailing_char = ' '
     BEGIN
       SELECT @output = @output + SUBSTRING(@string,1,@length) + CHAR(13)

       SELECT @string = SUBSTRING(@string,(@length + 1),(LEN(@string) - @length + 1))

       SELECT @string_length = LEN(@string)
     END
   ELSE
     -- find the first occurence of a blank space before the trailing space
     BEGIN
       DECLARE  @i INT

       SELECT @i = CHARINDEX(' ',REVERSE(SUBSTRING(@string,1,@length)))

       SELECT @output = @output + SUBSTRING(@string,1,(@length - @i)) + CHAR(13)

       SELECT @string = SUBSTRING(@string,(@length - @i + 2),(LEN(@string) - (@length - @i)))

       SELECT @string_length = LEN(@string)
     END
 END

 SELECT @output = @output + CHAR(13) + @string

 SELECT @output

GO

Taken from http://www.toadworld.com/platforms/sql-server/w/wiki/10085.word-wrap-a-string.aspx

@Nick It is a complex problem! All you need to do is paste this code into SQL Server Management Studio, run it to create the stored procedure. If you are unsure how to use stored procedures and functions there's lots of information out there... — Greg the Incredulous, Nov 06 '14 at 03:35
with the procedure above how would I use it in my query. SELECT Acct, Note, 'A1' AS Prefix FROM dbo.[RegionalOneNotesResults] UNION SELECT Acct, Note, 'B2' AS Prefix FROM dbo.[RegionalOneNotesResults] — Nick Giardelli, Nov 06 '14 at 13:28
@NickGiardelli if you change it to an SQL function then it's easy to use in a select statement. — Greg the Incredulous, Nov 06 '14 at 21:47

score 0 · Answer 2 · answered Nov 06 '14 at 03:06

0

I do not get the question. You want the string to be segmented by 250 characters but your desired output is only segregated by 156

declare @varSample varchar(800);

set @varSample='Blanket Lien Filed, account returned to Provider. Blah Blah will continue to follow and pursue lien payment. Called Pt Blahat xxx-xxx-xxxx. Left voice mail. Voicemail greeting did not state a name. Called Pt at xxx-xxx-xxxx. Left voice mail. Voicemail greeting did not state a name.';

SELECT SUBSTRING(@varSample, 1, 156) as Note, 'A1' AS  Prefix
UNION SELECT SUBSTRING(@varSample, 157, 300) as Note, 'B1' AS  Prefix

answered Nov 06 '14 at 03:06

TheProvost

1,832
2
16
41

Sorry, the copy did not complete, Yes I need it to be a max of 250 characters. It would look something like this instead of what I posted. Blanket Lien Filed, account returned to Provider. Blah Blah will continue to follow and pursue lien payment. Called Pt Blahat xxx-xxx-xxxx. Left voice mail. Blanket Lien Filed, account returned to Provider. Blah Blah will continue to follow and The max character limit is important but so is it not chopping a word in half. – Nick Giardelli Nov 06 '14 at 03:23

SQL String length limit but return multiple rows

2 Answers2