1

Struggling to join two text fields

,RTRIM(LTRIM(REPLACE(IM.ITEMDESC,',',''))) AS ITEMDESCR
,RTRIM(LTRIM(ISNULL(REPLACE(REPLACE(convert(varchar(MAX),[TXTFIELD]), 
CHAR(13),' '),',',''),''))) AS EXTRADESCRIPTION

Had to remove the carriage return on the second line - Have tried Concat and + but neither are working. Also have null values in the second line therefore if null value want to display the first line only.

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
Jamie hampson
  • 57
  • 1
  • 7

1 Answers1

0
create table im (id int not null identity(1,1), itemdesc varchar(64), txtfield text)
insert into im values 
 ('Pencil','Writing utensil')
,('Pen',null)
,(null,'Writing utensil')

select
    rtrim(ltrim(replace(im.itemdesc,',',''))) as itemdescr 
  , rtrim(ltrim(isnull(replace(replace(convert(varchar(max),txtfield), char(13),' '),',',''),''))) as extradescription
  , isnull(rtrim(ltrim(replace(im.itemdesc,',','')))+' ','')
  + isnull(rtrim(ltrim(replace(replace(convert(varchar(max),txtfield), char(13),' '),',',''))),'')
    as combined_description
from im

rextester demo: http://rextester.com/YQDARN44856

returns: 

+-----------+------------------+------------------------+
| itemdescr | extradescription |  combined_description  |
+-----------+------------------+------------------------+
| Pencil    | Writing utensil  | Pencil Writing utensil |
| Pen       |                  | Pen                    |
| NULL      | Writing utensil  | Writing utensil        |
+-----------+------------------+------------------------+
SqlZim
  • 37,248
  • 6
  • 41
  • 59
  • thank you , isnull(rtrim(ltrim(replace(im.itemdesc,',','')))+' ','') + isnull(rtrim(ltrim(replace(replace(convert(varchar(max),txtfield), char(13),' '),',',''))),'') as combined_description Worked great :) – Jamie hampson May 16 '17 at 20:55