Can large text fields be combined into a new field?

[RedSparks]

I have 4 Text fields that I need to combine into one new text field. Can this be done? If so, how do I go about making this happen.

Note
Goal_Note
Intervention_Note
Plan_Note
Note_All


I need the first 4 note fields combined into the last "Note_All" field.

I am using SQL Server 2000.

 

[djj55]

Off the top of my head look at UPDATETEXT and WRITETEXT. It has been a while and I do not remember all the needed code.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[MarkSweetland]

update tableName 
Set Note_All = isNull(Note,'') + isNull(Goal_Note, '') + isNull(Intervention_Note, '') + isNull(Plan_Note, '')

But you'll have to ensure the Note_All field is sized to accomodate the combined fields.


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[RedSparks]

Why do you have the isnull for each field? I don't want to combine null fields.

Also, from this information I'm still not able to combine 4 text(memo) fields and combine into the one "Note_All" text field.

 

[RedSparks]

Select (Note + case when Note is not null then '| 'end
+ Goal_Note + case when Goal_Note is not null then '| ' end
+ Intervention_Note + case when Intervention_Note is not null then '| ' end
+ Plan_Note) as All_Notes
From dbo.FD__CASE_MGMT_NOTE a

Set CONCAT_NULL_YIELDS_NULL OFF

I pulled this from one of the other posts and tried it out, as this is what I want this to do, but this does not work with the text(memo) field. Any thoughts on how to get this to work would be greatly appreciated.

 

[bborissov]

Why do you have the isnull for each field? I don't want to combine null fields.

ISNULLs are just for that NOT to combine NULL fields.

What give you this:

Select ISNULL(Note              + '| ', '') +
       ISNULL(Goal_Note         + '| ', '') +
       ISNULL(Intervention_Note + '| ', '') +
       ISNULL(Plan_Note               , '') as All_Notes
From dbo.FD__CASE_MGMT_NOTE a

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[markros]

If you're using TEXT fields, then as the first poster said, you need to check BOL for updatetext function.

PluralSight Learning Library