combine 2 text fields into one

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

General SQL Server Forums

New to SQL Server Programming

combine 2 text fields into one

Author

Topic

AgaK
Yak Posting Veteran

86 Posts

Posted - 2009-09-23 : 17:11:55

Hi

I am trying to combine 2 text fields into 1. The following script runs without errors but a portion of the fields get truncated. Each field contains max 8900 characters. The portion above 8000 gets truncated. Seems like the field get converted to char (max 8000) by substring.

DECLARE @Comments int
SET @Comments=ISNULL((SELECT max(DATALENGTH (Comments)) from Table1),0)
PRINT @Comments

DECLARE @Comments1 int
SET @Comments1=ISNULL((SELECT max(DATALENGTH (Comments1)) from Table1),0)
PRINT @TK_TTS_Comments

UPDATE Table1
SET Comments =
ISNULL(SUBSTRING ( Comments ,1,@Comments),'')
+ ' '
+ ISNULL(SUBSTRING ( Comments1 ,1,@Comments1),' ')
from Table1

Any idea how to combine 2 text fields into one?

Thank you

AK

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-09-23 : 19:16:16

Which version of SQL Server are you using?

What is the datatype definition of the Comments column? TEXT, NTEXT, VARCHAR(MAX), or NVARCHAR(MAX)?

CODO ERGO SUM

AgaK
Yak Posting Veteran

86 Posts

Posted - 2009-09-24 : 12:13:20

SQL 2005 and text datatype definition. But their is never more than 8600 charachers per field.

thank you!

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2009-09-24 : 14:35:18

Yes, the return type of SUBSTRING, for CHAR, VARCHAR or TEXT types is VARCHAR which maxes out at 8000 characters, so you are correct that SUBSTRING is truncating the information. Why are you using SUBSTRING when it appears you are trying to return the full value anyway? Just to handle NULL fields? It should work to just do

UPDATE Table1
SET Comments =
ISNULL(Comments,'')
+ ' '
+ ISNULL(Comments1,' ')
from Table1

--------------------------------------------
Brand yourself at EmeraldCityDomains.com

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-09-24 : 14:54:26

This shows an example of updating a TEXT column with a string with a lotal length of more than 8000 characters.


create table #MyTable (col text)

declare @c1 varchar(max)
declare @c2 varchar(max)
declare @c varchar(max)

select @c1 = replicate('a',8000)
select @c2 = replicate('b',8000)

select @c = @c1+@c2+@c1

insert into #MyTable (col) select ''

update #MyTable set col = @c

select
	[Col_length] = datalength(col),
	[@c_length]  = datalength(@c)
from
	#MyTable

drop table #MyTable

Results:



(1 row(s) affected)


(1 row(s) affected)

Col_length  @c_length            
----------- -------------------- 
24000       24000

(1 row(s) affected)

CODO ERGO SUM

AgaK
Yak Posting Veteran

86 Posts

Posted - 2009-09-24 : 16:07:00

Hi

Thank you for your replies.

In my situation the comments and comments1 fields have more than 8000 charters each. 8900 for comments and 3600 comments1. How can I combine them into one text field? The update statement truncates characters from comment field.

Thank you again

AK

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2009-09-24 : 16:22:37

quote:
Originally posted by AgaK

The update statement truncates characters from comment field.

So, just to be sure...you're saying that you tried my suggestion to try it without the SUBSTRING function and that it still is not working?

--------------------------------------------
Brand yourself at EmeraldCityDomains.com

AgaK
Yak Posting Veteran

86 Posts

Posted - 2009-09-24 : 19:09:38

Hi

I am getting the following error message when I run the update statmment:

Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals add, type equals text.
Somehow it doesn't like to concatenate the text fields.

Aga

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2009-09-24 : 20:06:12

Sorry, my mistake. + cannot be used in Transact-SQL to concatenate text datatypes. I was thinking of SSIS.

You may end up having to use READTEXT and WRITETEXT to get this to work.

--------------------------------------------
Brand yourself at EmeraldCityDomains.com

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-09-25 : 09:35:27

Have you tried something like this?


UPDATE Table1
SET
	Comments =
		convert(varchar(max), Comments  ) +
		convert(varchar(max), Comments1 )

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources