How to remove specific text from rows in a column in query result

Transact-SQL
21

You have two field with the same name (HIPPO) - you might want to one of them.

select clientid
      ,ClientSort as ClientName
      ,'Y' as Enabled
      ,'N' as HIPPA
      ,matternum
      ,case
          when charindex(@s,[description])>0
          then left(cast([description] as varchar(max)),charindex(@s,[description])-1)
          else [description]
       end as MatterName
      ,'Y' as Enable
      ,'N' as HIPPA
      ,areaoflaw
  from matters
 order by Clientid
         ,matterid
;
22

If all you want to do is remove the carriage returns - then you could do something like this:

REPLACE(CAST([description] AS varchar(max)), char(13), '')

If you actually have a carriage return/line feed:

REPLACE(CAST([description] AS varchar(max)), char(13) + char(10), '')

Or

REPLACE(REPLACE(CAST([description] AS varchar(max)), char(13), ''), char(10), '')