Copy portion of text column to another text column

I need to copy everything past the first instance of char(10)+char(13) in a text column to a second text column in a different table. I tried using BOL examples
My goal is to copy out for every row in the mail table the text past the crl/lf into a second text column - the text can be greater than 8000 chars

I was able to use Pubs examples to move the pointer to a certain point in the text column using the below modified example:

Use pubs
DECLARE @val varbinary(16)
DECLARE @startpos int
SELECT @startpos = PATINDEX('%ample%',pr_info) FROM pub_info WHERE pub_id = '0736'
SELECT @val = TEXTPTR(pr_info)
FROM pub_info
WHERE pub_id = '0736'
READTEXT pub_info.pr_info @val @startpos 10

In my mail case it wasn't working
It errors with  "offset and length specified in READTEXT statement is greater than the actual data"

DECLARE @val varbinary(16)
DECLARE @startpos int
DECLARE @length int
SELECT @startpos = PATINDEX('%'+Char(10)+char(13)+'%',Email_Text) FROM mail
SELECT @length = MAX(DATALENGTH(Email_Text)) FROM mail
SELECT @val = TEXTPTR(Email_Text)
FROM mail
WHERE mailid = '1'
READTEXT mail.Email_Text @val @startpos @length
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You can use the CharIndex function to find the first occurence then use the Substring Function to select just the part after that occurence.
Anthony PerkinsCommented:
If you are attempting to update a text column than you need to use UPDATETEXT and not READTEXT.
Anthony PerkinsCommented:
I should also have clarified that since you are copying from one text column to another you will need to use two TEXTPTRs.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

dsteinschneiderAuthor Commented:
In response to Bill wouldn't I need to convert the text to varchar to use CharIndex and Substring? I can't do that because the text is longer than 8K chars
dsteinschneiderAuthor Commented:
In response to acperkins - I am first making sure that I can select the entire text past the char(10)+char(13) which so far isn't working - thanks for pointing out that when I updatetext to the second column I will need to use a second TEXTPTR.

BTW, the second text column will be empty before I copy the sub portion of the first text column into it. I thought you use WRITETEXT when the entire column is being replaced?
Anthony PerkinsCommented:
WRITETEXT still requires a TEXTPTR.  However, if the text you extract is less than 8000 you may be able to get away with jsut an UPDATE statement.
dsteinschneiderAuthor Commented:
I just did a DATALENGTH query for all the rows and many of them are over 8000 so I can't use UPDATE and SUBSTRING
Anthony PerkinsCommented:
Actually, just reread BOL for UPDATETEXT:


Is the name of the table and text, ntext, or image column to update. Table and column names must conform to the rules for identifiers. For more information, see Using Identifiers. Specifying the database name and owner names is optional.


Is a value that stores the pointer to the text, ntext or image data. text_ptr must be binary(16). To create a text pointer, execute an INSERT or UPDATE statement with data that is not NULL for the text, ntext, or image column. For more information about creating a text pointer, see either INSERT or UPDATE.


Ignored in Microsoft® SQL Server™ 2000. Logging is determined by the recovery model in effect for the database.


Is the actual text, ntext or image data to store. data can be a literal or a variable. The maximum length of text that can be inserted interactively with WRITETEXT is approximately 120 KB for text, ntext, and image data.

So it looks like you may have to use UPDATETEXT after all.
dsteinschneiderAuthor Commented:
I mean for the text column length for each row is over 8k - (not the row length)
dsteinschneiderAuthor Commented:
The bodies of the emails average about 15K. I have worked my way through all the BOL examples but none give me enough information to know how to move these 15K of data from one text column to another text column. I understand that I need to SET TEXTSIZE to a size larger than the largest email body I want to copy.
Anthony PerkinsCommented:
Ok, I need some more feedback:

1. Does the row in the destination table exist or not?
2. If it does exist, is it empty or Null?
3. Do you want to copy one row or more than one?
4. What are the names of the tables and columns involved?
dsteinschneiderAuthor Commented:
1. The row in the destination table does not exist
2 -
3. I am copying more than one row - actually quite a few the first run - then just new rows based on MailID
 - Source Table Mail with cols MailID (varchar15) and MailText (Text)
 - Destination Table MailBody with cols MailID (varchar15) and MailText (Text)

dsteinschneiderAuthor Commented:
I've spent more time poring over examples  - I've foudn a bunch that show you how to append or insert the text to a certain point in the destination text but none that show me how to select text from a certain point in the source text and forward for insertion in a destination text col
dsteinschneiderAuthor Commented:
Will this require copying the entire text to the new column (I've got that working now) and then replacing the beginning portion I don't want with an empty string?
Anthony PerkinsCommented:
>>Will this require copying the entire text to the new column (I've got that working now) and then replacing the beginning portion I don't want with an empty string?<<
Yes.  This is the only way, I could think of doing it.  In other words:
1. Do an INSERT statement to copy all the data as is.
2. Cycle through all the rows using a CURSOR
3. Use UPDATETEXT to update just the portion required.  Make sure that delete_length is nonnull
dsteinschneiderAuthor Commented:
I got this example from Ken Henderson working - does this make sense to just copy the entire text to my new table and then insert an empty string in place of everything up to the CR/LF ?

CREATE TABLE #testnotes (k1 int identity, notes text DEFAULT ' ')


UPDATE #testnotes
 SET notes='This is before the crlf testing 123 testing '+CHAR(10)+CHAR(13)+'This is after the crlf'

  @textptr binary(16)
, @patindex int
, @patlength int
, @patindex=PATINDEX('%'+CHAR(10)+CHAR(13)+'%',notes)+1
, @patlength=DATALENGTH(notes)- (DATALENGTH(notes) - @patindex)
FROM #testnotes (UPDLOCK)
WHERE PATINDEX('%'+CHAR(10)+CHAR(13)+'%',notes)<>0

UPDATETEXT #testnotes.notes @textptr 0 @patlength ''

SELECT * FROM #testnotes


DROP TABLE #testnotes
Anthony PerkinsCommented:
You need to use a CURSOR to get the TEXTPTR (@TextPointer), string position (@InsertOffset) and string length (@DeleteLength) for each row.  You can then cycle through all the rows and execute something like the following for each row:

Set @InsertOffset = @InsertOffset - 1
UPDATETEXT #testnotes.notes @TextPointer @InsertOffset @DeleteLength

If you are strill struggling with this I will take a look at it tonight when I get home.
dsteinschneiderAuthor Commented:
This isn't pretty but its doing the job:

CREATE PROCEDURE dbo.sp_copy_text_in_record
  @src_record_key  as varchar(15),
  @dst_record_key  as varchar(15)
  DECLARE @readptrval     as binary(16),
          @writeptrval    as binary(16),
          @content_length as bigint,
          @ErrorSave      as int,
        @textptr binary(16),
         @patindex int,
          @patlength int
  SELECT @@TEXTSIZE  -- this may be a limit to be aware of

  -- Find the pointer to the text field in the source record
  SELECT @readptrval = TEXTPTR(MailText),
         @content_length = datalength(MailText)
  FROM   Mail sm
  WHERE  sm.MailID = @src_record_key
  PRINT 'Length of Content = '+convert(varchar(20),@content_length)  -- just to see length, not necessary

  -- Find the pointer to the text field in the destination record being updated

 SELECT @writeptrval = TEXTPTR(MailBody)
  FROM   MailBody dm
  WHERE  dm.MailID = @dst_record_key

  UPDATETEXT MailBody.MailBody @writeptrval 0 NULL Mail.MailText @readptrval

 , @patindex=PATINDEX('%'+CHAR(10)+CHAR(13)+'%',MailBody)+1
 , @patlength=DATALENGTH(MailBody)- (DATALENGTH(MailBody) - @patindex)
 WHERE PATINDEX('%'+CHAR(10)+CHAR(13)+'%',MailBody)<>0

 UPDATETEXT MailBody.MailBody @textptr 0 @patlength ''

  IF @@ERROR <> 0
      PRINT 'ERROR on text field copy - returning error'
      SELECT 'ERROR on text field copy - returning error' AS 'return_code'
      SELECT @ErrorSave = @@ERROR
      RETURN @ErrorSave

dsteinschneiderAuthor Commented:
I've realized that for the existing data I can INSERT INTO to make a copy of the entire table and drop the portion of the sp that copies the text data. Can you give me an example using cursors to do this part?

 , @patindex=PATINDEX('%'+CHAR(10)+CHAR(13)+'%',MailBody)+1
 , @patlength=DATALENGTH(MailBody)- (DATALENGTH(MailBody) - @patindex)
 WHERE PATINDEX('%'+CHAR(10)+CHAR(13)+'%',MailBody)<>0

 UPDATETEXT MailBody.MailBody @textptr 0 @patlength ''

Thanks again
Anthony PerkinsCommented:
Try something like this:

1. Do an INSERT statement to copy all the data as is.
INSERT Destination (MailBody)
Select   MailBody
From    Source
Where  SomeCondition

2. Cycle through all the rows using a CURSOR
3. Use UPDATETEXT to update just the portion required.

DECLARE @TextPointer binary(16),
      @DeleteLength integer

Select      TEXTPTR(MailBody),
      CHARINDEX(CHAR(10), MailBody)
From Destination
Where SomeCondition

OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @TextPointer, @DeleteLength

      UPDATETEXT Destination.MailBody @TextPointer 0 @DeleteLength
      FETCH NEXT FROM MyCursor INTO @TextPointer, @DeleteLength

CLOSE MyCursor

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dsteinschneiderAuthor Commented:
Thanks ac,

I changed it to CHARINDEX(CHAR(10)+CHAR(13),MailBody) and it worked great.
Anthony PerkinsCommented:
>>I changed it to CHARINDEX(CHAR(10)+CHAR(13),MailBody) and it worked great. <<
Check carefully, as you may be leaving an extra CHAR(13) at the start.  In other words, I think it should be:
Select     TEXTPTR(MailBody),
     CHARINDEX(CHAR(10) + CHAR(13), MailBody) + 1
From Destination
Where SomeCondition
dsteinschneiderAuthor Commented:
You're right - there was a "leading cr/lf" but that looked ok in the report we're running - I changed it to +1

Thanks again for all your help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.