Monday, October 31, 2016

SQL to Excel Cleanup - remove tabs, new lines, etc

DECLARE @CrLf CHAR(2);
SET @CrLf = CHAR(13) + CHAR(10); -- CarriageReturn + LineFeed

SELECT [PROB_TYPE]
 ,[CONTACTNAME]
      ,[TECHNITION]
      ,[NUMBER]
 --,[DESCRIPTION]
 ,REPLACE(REPLACE(SUBSTRING([DESCRIPTION],1,DATALENGTH([DESCRIPTION])),@CrLf,'-'), CHAR(9), '') AS [DESCRIPTION]
      ,[OPENED]
      ,[CLOSED]
      ,[CLOSINGREMARKS]
      ,[STATUS]
      ,[ENTERED_BY]
      ,[CONT_PHONE]
      ,[CONT_ADDRESS]
      ,[CONT_EMAIL]
      ,[TAG_NO]
      ,[SERIAL_NO]
      ,[MODEL]
      ,[PRIORITY]
      ,[SHOW]
      ,[CODE]
      ,[CLOSEID]
      ,[EMERGENCY_CHANGE]
      ,[ENTERED_ID]
      ,[HRS_EST]
      ,[HRS_ACTUAL]
  FROM [problems]
  where status not in ('closed')
 order by prob_type