recognized - trim sql server 2014

Cómo usar una función TRIM en SQL Server

Question

No puedo hacer funcionar este código TRIM

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

Ejemplo:

DECLARE @Str NVARCHAR(MAX) = N''
foo bar
Foo Bar
''
PRINT ''['' + @Str + '']''
DECLARE @StrPrv NVARCHAR(MAX) = N''''
WHILE ((@StrPrv <> @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 + '']''

Resultado

[
foo bar
Foo Bar
]
[foo bar
Foo Bar]

Usando fnTrim

Fuente: https://github.com/reduardo7/fnTrim

SELECT dbo.fnTrim(colName)

Answer 2

Faltan dos paréntesis de cierre ... y no estoy seguro de que un signo funcione como un operador de concatenación de cadenas. Intenta ''+''

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 3

TRIM todos los TAB SPACE y 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 + ''"''

Como funcion

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

Ejemplo

-- 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) + ''"''

Resultado

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

Answer 4

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

Creo que te estás perdiendo a ) en ambos ajustes. Algunas versiones de SQL solo admiten TRIM, que realiza ajustes L y R ...