Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2006-06-18
6
Medium Priority
?
206 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 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

972 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