concatenate text columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

concatenate text columns

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-04-21 : 07:26:13

How do I concatenate text columns?

This doesnt work because you have to do a CAST:
INSERT INTO Target (TextNotesColumn)
SELECT S.TextNotesColumn1 + TextNotesColumn2 + 'a string' + VarcharColumn
FROM Source S

But if a do a CAST to VARCHAR like this:
INSERT INTO Target (TextNotesColumn)
SELECT CAST(S.TextNotesColumn1 AS VARCHAR(8000)) + CAST(S.TextNotesColumn2 AS VARCHAR(8000)) + 'a string' + S.VarcharColumn
FROM Source S

sql rightly stops me because it would result in data loss as I truncate the text column to varchar8000.

So how can I do it then?

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-04-21 : 14:18:51

What in the world would you want to do this for? As you know, TEXT columns are HUGE and are not normally used for storing verbage that needs to be manipulated like this. Can you come up with a different approach that does not require concatenating these, and still meet your needs?

---------------------------
EmeraldCityDomains.com

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-04-22 : 05:12:01

I wish. I agree with your wise opinion. Unfortunately my hands are tied. This decision is out of my hands.

andy8979
Starting Member

36 Posts

Posted - 2005-04-22 : 08:05:28

INSERT INTO Target (TextNotesColumn)
SELECT CAST(S.TextNotesColumn1 AS VARCHAR(8000))

after this update the TextNotesColumn column in the Target table with the other text for the particular row.

or you could get the details of the string in a varchar variable and then insert the record in the table
like:
Declare @txt varchar(8000)
Declare @txt1 varchar(8000)
Declare @txt2 varchar(8000)

Set @txt1 = 'ajdslkfjasdj'
Set @txt2 = 'ajdslkfjasdj'

set @txt = @txt1 + @txt2

Declare @tab Table (txt varchar(8000))

Insert into @tab values (@txt)
Select * from @tab

but it is mandatory that the text length should be less than 8000 characters if you using varchar datatype

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-04-22 : 08:51:55

I think the answer is: it can be done
so i used a dts transform with vbscript instead

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources