?
Solved

Copy portion of text column to another text column

Posted on 2006-03-28
23
Medium Priority
?
700 Views
Last Modified: 2011-08-18
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
GO
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
GO

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
0
Comment
Question by:dsteinschneider
  • 13
  • 9
23 Comments
 
LVL 11

Expert Comment

by:BillPowell
ID: 16312079
You can use the CharIndex function to find the first occurence then use the Substring Function to select just the part after that occurence.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16312689
If you are attempting to update a text column than you need to use UPDATETEXT and not READTEXT.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16312707
I should also have clarified that since you are copying from one text column to another you will need to use two TEXTPTRs.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 1

Author Comment

by:dsteinschneider
ID: 16312834
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
0
 
LVL 1

Author Comment

by:dsteinschneider
ID: 16312939
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?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16312995
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.
0
 
LVL 1

Author Comment

by:dsteinschneider
ID: 16313054
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16313056
Actually, just reread BOL for UPDATETEXT:

<quote>
Arguments
table.column

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.

text_ptr

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.

WITH LOG

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

data

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.
</quote>

So it looks like you may have to use UPDATETEXT after all.
0
 
LVL 1

Author Comment

by:dsteinschneider
ID: 16313069
I mean for the text column length for each row is over 8k - (not the row length)
0
 
LVL 1

Author Comment

by:dsteinschneider
ID: 16316006
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16317670
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?
0
 
LVL 1

Author Comment

by:dsteinschneider
ID: 16321975
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
4.
 - Source Table Mail with cols MailID (varchar15) and MailText (Text)
 - Destination Table MailBody with cols MailID (varchar15) and MailText (Text)

Thanks
0
 
LVL 1

Author Comment

by:dsteinschneider
ID: 16323484
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
0
 
LVL 1

Author Comment

by:dsteinschneider
ID: 16323497
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?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16324529
>>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
0
 
LVL 1

Author Comment

by:dsteinschneider
ID: 16324670
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 ' ')

BEGIN TRAN
INSERT #testnotes DEFAULT VALUES

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

DECLARE
  @textptr binary(16)
, @patindex int
, @patlength int
SELECT
  @textptr=TEXTPTR(notes)
, @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

COMMIT TRAN

GO
DROP TABLE #testnotes
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16325096
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.
0
 
LVL 1

Author Comment

by:dsteinschneider
ID: 16325268
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)
AS
  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

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

 UPDATETEXT MailBody.MailBody @textptr 0 @patlength ''

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


GO
0
 
LVL 1

Author Comment

by:dsteinschneider
ID: 16325398
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?

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

 UPDATETEXT MailBody.MailBody @textptr 0 @patlength ''

Thanks again
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1000 total points
ID: 16328891
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

DECLARE MyCursor CURSOR FOR
Select      TEXTPTR(MailBody),
      CHARINDEX(CHAR(10), MailBody)
From Destination
Where SomeCondition

OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @TextPointer, @DeleteLength

WHILE @@FETCH_STATUS = 0
   BEGIN
      UPDATETEXT Destination.MailBody @TextPointer 0 @DeleteLength
      FETCH NEXT FROM MyCursor INTO @TextPointer, @DeleteLength
   END

CLOSE MyCursor
DEALLOCATE MyCursor


0
 
LVL 1

Author Comment

by:dsteinschneider
ID: 16333830
Thanks ac,

I changed it to CHARINDEX(CHAR(10)+CHAR(13),MailBody) and it worked great.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16333919
>>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
0
 
LVL 1

Author Comment

by:dsteinschneider
ID: 16334653
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
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question