Geeks With Blogs
Razi Syed Professional Developer

If you want to concatenate (append to end) a string to a field in SQL, there is a very easy and simple update statement for that:

Update [tableName] Set [myField] = [myField] + 'whatever' Where [id] = 1

The + operator works fine for strings, as long as the field is varchar or nvarchar data type. If it is text or ntext, you cannot use the + operator to join strings, and you will get the follow sql error:

"Invalid operator for data type. Operator equals add, type equals text"

Which is pretty annoying. Anyway thats how Micrsoft designed it (see msdn documentation: http://msdn2.microsoft.com/en-us/library/aa276862(SQL.80).aspx).

There is a work around. You have to use the SQL UpdateText command instead of the Update command, which is a bit more complicated than Update. The following is a stored procedure that you can use to append a string to end of a text field. The UpdateText Command updates only one row at a time, so the following proceduce opens a cursor to go through all rows that need to append, and appends the given text.

-- =============================================
-- Author: Syed,Razi
-- Description: Appends to the AgNotes field
-- Assumptions: You have a SQL Database table myTable, with 
--                      field 'ID' that is used to select rows and
--                      field 'textField' as a field of data type text
-- =============================================

CREATE PROCEDURE [spAppendToTextField]
    @TextToAdd nvarchar(255),  -- Text to append to the end of the field
   
@ID int   -- Primary ID of the field
AS

BEGIN

DECLARE @myPtr binary(16) -- This will be used to store a TEXTPTR to text field
DECLARE @n int                   -- This will be used to store the length of the text field

DECLARE
myCursor CURSOR FOR
    -- Select a text pointer to the text data type field, and get the current length
  
SELECT TEXTPTR(textField), DATALENGTH(textField)
   
FROM myTable
  
WHERE ID= @ID

OPEN myCursor

FETCH NEXT FROM myCursor
  
INTO @myPtr, @n

WHILE @@FETCH_STATUS = 0
 -- Go through each row to update
BEGIN

     -- Append the text to the end of currently selected row
     -- UpdateText Syntax:
     --   UPDATETEXT [table].[field] [TEXTPTR] [Start_Insert_Position] [Num_to_Delete] [TextToInsert]

   
UPDATETEXT myTable.textField @myPtr @n 0 @TextToAdd

   
FETCH NEXT FROM myCursor
        
INTO @myPtr, @n

END

END

CLOSE myCursor
DEALLOCATE myCursor

To run this stored procedure just use:EXEC spAppendToTextField 'Append This Text', 4

 

Google
Posted on Friday, April 25, 2008 5:29 PM SQL Server 2000 , SQL Server 2005 | Back to top


Comments on this post: How to append to a sql text data type feild

# How to Fetch a sql text data type feild
Requesting Gravatar...
Please tell me same procedure for getting text data type
Left by bond on Nov 07, 2008 6:14 AM

# re: How to append to a sql text data type feild
Requesting Gravatar...
This is my first go at working with SQL and I am trying to update text field fldPopUp where fldEmail in NULL based on fldPK -- I've modified the SP to point to the appropriate table and field and if I run the EXEC command with a specific ID it works but I was trying to systematically update all rows where fldEmail is Null by Primary Key value for each record. How to I pass that info systematically into my EXEC command?
Left by James on Jan 03, 2010 9:20 AM

# re: How to append to a sql text data type feild
Requesting Gravatar...
Hi Razi,

I just started teaching myself SQL for my company's website and I am trying to learn how to add additional text to a text field that already contains text without deleting the original text.

I've tried to execute the command below but had no success.

SELECT searchKeywords2
FROM products
INSERT INTO products (searchKeywords2) VALUES (TEST4)
WHERE (searchKeywords2 LIKE N'%TEST1%')

Again, I am a complete novice so I apologize if my post is annoying to you.
Please help me!
Left by Danner Chang on Apr 26, 2012 7:32 PM

# re: How to append to a sql text data type feild
Requesting Gravatar...
Hi again,

I just used the following code...

UPDATETEXT products.searchkeywords2 @myPtr @n 20 @TEST4
WHERE (searchKeywords2 LIKE '%test1%')

and I received a "This designer does not graphically support the UPDATETEXT SQL construct."

I am using Enterprise Manager 2000.

Any suggestions?
Left by Danner Chang on Apr 26, 2012 8:26 PM

Your comment:
 (will show your gravatar)


Copyright © razisyed | Powered by: GeeksWithBlogs.net