How to concat multiple fields in query?

[Steve Zalinski]

This returns only txt1. Any thoughts?

Columns are (varchar (250),Null).

select distinct 
employee_name, 
tran_date, 
billed_hrs, 
(txt1 + txt2 + txt3 + txt4 +txt5 + txt6) as Narrative  
 
from tat_time 
left outer join hbm_matter on
tat_time.matter_uno = hbm_matter.matter_uno
left outer join hbm_persnl on
tat_time.tk_empl_uno = hbm_persnl.empl_uno
left outer join tat_text on
tat_time.time_uno = tat_text.source_uno
 
where clnt_matt_code = '013187.0000041'
and wip_status = 'B'
and billed_hrs != 0
order by tran_date

[gpl]

What does your data look like ?

[Sandeep M]

try this
......

(isnull(txt1,'') +isnull(txt2,'') + isnull(txt3,'') + isnull(txt4,'')+isnull(txt5,'') + isnull(txt6,'')) as Narrative

[Steve Zalinski]

The date is narrative in a time and billing system. Once 250 characters are used in txt1, txt2 is used, and so on. Narrative like "Conference with so and so regarding such and such."

[ck9663]

First, you can actually increase your varchar length more than 250...

In your case, try to isolate the problem first...are all those txt<x> columns coming from a single table? Maybe one or some of those are NULL. Try doing with Sandeep recommended. If you want to isolate your problem, replace the "+" sign with comma, remove the "as Narrative" and the parenthesis, that way you'll know what the value of each of those fields are. If the other txt<x> fields are empty, then it will not be concatenated. You have to check if these columns are properly populated...

Good Luck!!!

~~ CK

[Steve Zalinski]

Thank you both. But Isnull does not help. With or without isnull, it picks up the first 6 character in txt2 then drops the remainder of txt2 and nothing beyond that. Yes, I can get the full text in each column using commas and concatenate them in Excel. Yes, the columns are all in one table. Can't change the properties of the tables or columns. In some cases, txt2, txt3, txt4, etc may or may not be populated.

[Steve Zalinski]

It seems the problem is my version of SQL Analyser 8.0 is the culprit. Tried a more elaborate script written by one of our prgrammers:

'DESC_COMMENTS'=ISNULL(CONVERT(TEXT,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
 
      (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(TXT1)),
 
      CHAR(10),Space(1)),CHAR(13),Space(1)),CHAR(9),Space(1)),CHAR(10)+CHAR(13),Space(1)),CHAR(13)+CHAR(10),SPACE(1)),
 
      Space(5),Space(1)),Space(4),Space(1)),Space(3),Space(1)),Space(2),Space(1)) + 
 
      REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(TXT2)),
 
      CHAR(10),Space(1)),CHAR(13),Space(1)),CHAR(9),Space(1)),CHAR(10)+CHAR(13),Space(1)),CHAR(13)+CHAR(10),SPACE(1)),
 
      Space(5),Space(1)),Space(4),Space(1)),Space(3),Space(1)),Space(2),Space(1)) + 
 
      REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(TXT3)),
 
      CHAR(10),Space(1)),CHAR(13),Space(1)),CHAR(9),Space(1)),CHAR(10)+CHAR(13),Space(1)),CHAR(13)+CHAR(10),SPACE(1)),
 
      Space(5),Space(1)),Space(4),Space(1)),Space(3),Space(1)),Space(2),Space(1)) + 
 
      REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(TXT4)),
 
      CHAR(10),Space(1)),CHAR(13),Space(1)),CHAR(9),Space(1)),CHAR(10)+CHAR(13),Space(1)),CHAR(13)+CHAR(10),SPACE(1)),
 
      Space(5),Space(1)),Space(4),Space(1)),Space(3),Space(1)),Space(2),Space(1)) + 
 
      REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(TXT5)),
 
      CHAR(10),Space(1)),CHAR(13),Space(1)),CHAR(9),Space(1)),CHAR(10)+CHAR(13),Space(1)),CHAR(13)+CHAR(10),SPACE(1)),
 
      Space(5),Space(1)),Space(4),Space(1)),Space(3),Space(1)),Space(2),Space(1)) + 
 
      REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(TXT6)),
 
      CHAR(10),Space(1)),CHAR(13),Space(1)),CHAR(9),Space(1)),CHAR(10)+CHAR(13),Space(1)),CHAR(13)+CHAR(10),SPACE(1)),
 
      Space(5),Space(1)),Space(4),Space(1)),Space(3),Space(1)),Space(2),Space(1))),
 
      CHAR(10),Space(1)),CHAR(13),Space(1)),CHAR(9),Space(1)),CHAR(10)+CHAR(13),Space(1)),CHAR(13)+CHAR(10),SPACE(1)),
 
      Space(5),Space(1)),Space(4),Space(1)),Space(3),Space(1)),Space(2),Space(1))),'No narrative.')
 

which works on 2005 but still comes up short on 8.0. I'm going to upgrade. Thanks for all your help.