?
Solved

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

Posted on 2006-06-18
6
Medium Priority
?
203 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 225 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 525 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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 set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

752 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