Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

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

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
stev0931
Asked:
stev0931
  • 3
  • 2
2 Solutions
 
LowfatspreadCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
stev0931Author Commented:
Is there a way to do this in a single TSQL statement?  Without all the code above?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Aneesh RetnakaranDatabase AdministratorCommented:
You should do like this ? The text fields are very difficult to maintain, you can create a stored procedure for this purpose
0
 
stev0931Author Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hope you got the answer... Thanks for the GRADE 'B'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now