如何在 SQL Server 中使用 TRIM 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14447350/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use a TRIM function in SQL Server
提问by user1958651
I cannot get this TRIM code to work
我无法使用此 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]
回答by maelstrom
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]
回答by Eduardo Cuomo
TRIMall SPACE's TAB's and ENTER's:
TRIM所有SPACE的TAB年代和ENTER的：
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 ]" |
+-------+----------------+
回答by xQbert
LTRIM(RTRIM(FCT_TYP_CD)) & ') AND (' & LTRIM(RTRIM(DEP_TYP_ID)) & ')'
I think you're missing a )on both of the trims. Some SQL versions support just TRIM which does both L and R trims...
我认为你)在两个装饰上都缺少一个。某些 SQL 版本仅支持 TRIM，它可以同时进行 L 和 R 修剪...
回答by Eduardo Cuomo
Example:
例子：
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 + ']'
Result
结果
[
foo bar
Foo Bar
]
[foo bar
Foo Bar]
Using fnTrim
使用 fnTrim
Source: https://github.com/reduardo7/fnTrim
来源：https: //github.com/reduardo7/fnTrim
SELECT dbo.fnTrim(colName)
