How to use a TRIM function in SQL Server

Question

I cannot get this TRIM code to work

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] 

Answer 1

You are missing two closing parentheses...and I am not sure an ampersand works as a string concatenation operator. Try '+'

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] 

Answer 2

TRIM all SPACE's TAB's and ENTER's:

DECLARE @Str VARCHAR(MAX) = '                 [         Foo    ]                  '  DECLARE @NewStr VARCHAR(MAX) = '' DECLARE @WhiteChars VARCHAR(4) =       CHAR(13) + CHAR(10) -- ENTER     + CHAR(9) -- TAB     + ' ' -- SPACE  ;WITH Split(Chr, Pos) AS (     SELECT           SUBSTRING(@Str, 1, 1) AS Chr         , 1 AS Pos     UNION ALL     SELECT           SUBSTRING(@Str, Pos, 1) AS Chr         , Pos + 1 AS Pos     FROM Split     WHERE Pos <= LEN(@Str) ) SELECT @NewStr = @NewStr + Chr FROM Split WHERE     Pos >= (         SELECT MIN(Pos)         FROM Split         WHERE CHARINDEX(Chr, @WhiteChars) = 0     )     AND Pos <= (         SELECT MAX(Pos)         FROM Split         WHERE CHARINDEX(Chr, @WhiteChars) = 0     )  SELECT '"' + @NewStr + '"' 

As Function

CREATE FUNCTION StrTrim(@Str VARCHAR(MAX)) RETURNS VARCHAR(MAX) BEGIN     DECLARE @NewStr VARCHAR(MAX) = NULL      IF (@Str IS NOT NULL) BEGIN         SET @NewStr = ''          DECLARE @WhiteChars VARCHAR(4) =               CHAR(13) + CHAR(10) -- ENTER             + CHAR(9) -- TAB             + ' ' -- SPACE          IF (@Str LIKE ('%[' + @WhiteChars + ']%')) BEGIN              ;WITH Split(Chr, Pos) AS (                 SELECT                       SUBSTRING(@Str, 1, 1) AS Chr                     , 1 AS Pos                 UNION ALL                 SELECT                       SUBSTRING(@Str, Pos, 1) AS Chr                     , Pos + 1 AS Pos                 FROM Split                 WHERE Pos <= LEN(@Str)             )             SELECT @NewStr = @NewStr + Chr             FROM Split             WHERE                 Pos >= (                     SELECT MIN(Pos)                     FROM Split                     WHERE CHARINDEX(Chr, @WhiteChars) = 0                 )                 AND Pos <= (                     SELECT MAX(Pos)                     FROM Split                     WHERE CHARINDEX(Chr, @WhiteChars) = 0                 )         END     END      RETURN @NewStr END 

Example

-- Test DECLARE @Str VARCHAR(MAX) = '                 [         Foo    ]                      '  SELECT 'Str', '"' + dbo.StrTrim(@Str) + '"' UNION SELECT 'EMPTY', '"' + dbo.StrTrim('') + '"' UNION SELECT 'EMTPY', '"' + dbo.StrTrim('      ') + '"' UNION SELECT 'NULL', '"' + dbo.StrTrim(NULL) + '"' 

Result

+-------+----------------+ | Test  | Result         | +-------+----------------+ | EMPTY | ""             | | EMTPY | ""             | | NULL  | NULL           | | Str   | "[   Foo    ]" | +-------+----------------+