Solved

Quick Question on appending to a text data type in SQL Server

Posted on 2006-06-18
6
197 Views
Last Modified: 2008-03-03
It is my understanding that you cannot directly append to a text data type (since you get an error).  I have tried a number of things without success.  I need to append a string to a text data type without getting a bunch of spaces at the end.  It would be preferable if the returned value was of type text, but it's not necessary.



This is one of the statements I've been working on, but it doesn't work:
select '(OLD) ' + convert(char(datalength(itemname)+6), itemname) from items where id = 30

Note that itemname is of type text and that, even if this did work, it wouldn't return a text data type (although, again, this is not necessary - just preferable)
0
Comment
Question by:stev0931
  • 3
  • 2
6 Comments
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 75 total points
ID: 16930890
which version of SQL Server....?

SQL Server 2005 introduces the Varchar(MAX)  data type to replace TEXT...

in prior versions you need to use text pointers and the WRITETEXT / UPDATETEXT commands

see Books on Line...

0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 175 total points
ID: 16931513
Hi stev0931,
You need to use WriteText or UpdateText for this purpose ... here is one example



-- create table table1 ( id int identity, Fields1 text, status bit )
-- create table table2 (id int identity, Fields2 varchar(255),flag_field bit)

-- insert into table1 SELECT replicate('a',20),1
-- insert into table1 SELECT replicate('b',20),1
-- insert into table1 SELECT replicate('c',20),1
-- insert into table1 SELECT replicate('d',20),1
-- insert into table1 SELECT replicate('e',20),1
-- insert into table1 SELECT replicate('f',20),1
-- insert into table2 select ' and b',1
-- insert into table2 select ' and c' ,0
-- insert into table2 select ' and d' ,1
-- insert into table2 select 'and no',1

SET NOCOUNT ON

BEGIN TRAN
SELECT * from table1

DECLARE @Fields2 varchar(255)
DECLARE @flag_field bit

DECLARE @txtPtr1 Varbinary(16)
set @txtPtr1 = 0
IF OBJECT_ID('tempdb..#tabe') IS NOT NULL
DROP TABLE #tabe
CREATE TABLE #tabe (i int )
declare @id int

INSERT INTO #tabe
SELECT table1.id
FROM table2, table1
WHERE table1.id = table2.id and table1.status = 1


WHILE @txtPtr1 IS NOT NULL
BEGIN
    SET @txtPtr1 = NULL
    SELECT TOP 1 @txtPtr1 = TEXTPTR(Fields1),
        @flag_field =flag_field,
        @Fields2 = Fields2,
        @id = table1.id
        FROM table2, table1,#tabe t3
    WHERE table1.id = table2.id and table1.status = 1 and table1.id = t3.i
   
    IF @txtPtr1 IS NOT NULL
    BEGIN
        IF @flag_field = 1
            UPDATETEXT table1.Fields1 @txtPtr1 NULL 0 @Fields2
        ELSE
            WRITETEXT table1.Fields1 @txtPtr1 @Fields2


END
DELETE FROM #Tabe
WHERE i = @id
END

DROP TABLE #tabe
SELECT * from table1
ROLLBACK TRAN

Aneesh R!
0
 

Author Comment

by:stev0931
ID: 16947012
Is there a way to do this in a single TSQL statement?  Without all the code above?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16957127
You should do like this ? The text fields are very difficult to maintain, you can create a stored procedure for this purpose
0
 

Author Comment

by:stev0931
ID: 16964266
Then is there a way to convert the statement below

select '(OLD) ' + convert(char(datalength(itemname)+6), itemname) from items where id = 30

to correct syntax?  Note this statement would return a char type if it could execute
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17024307
Hope you got the answer... Thanks for the GRADE 'B'
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

790 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