SELECT
   dbo.COL_V_Cost_GEMS_Detail.TNG_SYS_NR AS [EHP Code],
   dbo.COL_TBL_VCOURSE.TNG_NA AS [Course Title],
   LTRIM(RTRIM(FCT_TYP_CD)& ') AND (' & LTRIM(RTRIM(DEP_TYP_ID) & ')' AS [Course Owner]

SELECT dbo.COL_V_Cost_GEMS_Detail.TNG_SYS_NR AS [EHP Code],
dbo.COL_TBL_VCOURSE.TNG_NA AS [Course Title],
LTRIM(RTRIM(FCT_TYP_CD)) + ') AND (' + LTRIM(RTRIM(DEP_TYP_ID)) + ')' AS [Course Owner]

LTRIM(RTRIM(FCT_TYP_CD)) & ') AND (' & LTRIM(RTRIM(DEP_TYP_ID)) & ')'

SELECT
    -- TRIM
    LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(columnname, CHAR(9), ' '), CHAR(13), ' '), CHAR(10), ' ')))