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  amp     AND     amp  LTRIM RTRIM DEP TYP ID   amp      AS  Course Owner

User · Answer

LTRIM RTRIM FCT TYP CD    amp     AND     amp  LTRIM RTRIM DEP TYP ID    amp        I think you re missing a   on both of the trims   Some SQL versions support just TRIM which does both L and R trims

User · Answer

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

User · Answer

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  lt   LEN  Str    SELECT  NewStr    NewStr   Chr FROM Split WHERE     Pos  gt             SELECT MIN Pos          FROM Split         WHERE CHARINDEX Chr   WhiteChars    0           AND Pos  lt             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  lt   LEN  Str                            SELECT  NewStr    NewStr   Chr             FROM Split             WHERE                 Pos  gt                         SELECT MIN Pos                      FROM Split                     WHERE CHARINDEX Chr   WhiteChars    0                                   AND Pos  lt                         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          ------- ----------------

User · Answer

Example   DECLARE  Str NVARCHAR MAX    N              foo   bar         Foo           Bar             PRINT        Str        DECLARE  StrPrv NVARCHAR MAX    N    WHILE    StrPrv  lt  gt   Str  AND   Str IS NOT NULL   BEGIN     SET  StrPrv    Str      -- Beginning     IF EXISTS  SELECT 1 WHERE  Str LIKE       CHAR 13    CHAR 10    CHAR 9                  SET  Str   LTRIM RIGHT  Str  LEN  Str  - 1        -- Ending     IF EXISTS  SELECT 1 WHERE  Str LIKE        CHAR 13    CHAR 10    CHAR 9                 SET  Str   RTRIM LEFT  Str  LEN  Str  - 1   END  PRINT        Str         Result                foo   bar         Foo           Bar             foo   bar         Foo           Bar    Using fnTrim  Source  https   github com reduardo7 fnTrim  SELECT dbo fnTrim colName

[sql] How to use a TRIM function in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to trim