Solved

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

Posted on 2006-06-18
6
188 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now