我正在编写一个UDF，它在文本字符串中搜索不同的空格分隔符组合(例如，回车符、换行符、空格等)，并用不同的非空格分隔符替换它们.
我在表变量中插入了所有可能的分隔符组合以及新的替换分隔符；然而，当我使用replace()函数时，它与我预期的分隔符不匹配.结果是，如果我的DELIMITERS表中有一行的分隔符长度为3个字符，而其他行只有2个字符长度的分隔符，那么它就是在只有2个字符分隔符的行上插入一个尾随空格.
我还注意到，如果在INSERT过程中仅在一行的任何单个或CHAR()的组合周围添加一个TRIM()，则会删除所有行的尾随空格.
我只是想了解为什么在INSERT期间添加尾随空格，以及为什么在INSERT期间只对一行使用TRIM()会影响尾随空格的所有行.
将MSSQL Server 14.0.3445 RTM与SQL_Latin1_General_CP1_CI_AS排序规则配合使用
以下是一个带有注释的示例，它解释了我的问题:
DECLARE @delimiters TABLE (Id INT, delim VARCHAR(10), newDelim CHAR)
INSERT INTO @delimiters
VALUES
(1, CHAR(10) + CHAR(32) + CHAR(13), '$'),
(2, CHAR(10) + CHAR(13), '#'),
(3, CHAR(13) + CHAR(10), '@')
DECLARE @strings TABLE (Id INT, [str] VARCHAR(100))
INSERT INTO @strings
VALUES
(1, 'This is paragraph 1.' + CHAR(10) + CHAR(32) + CHAR(13) + 'This is paragraph 2.'),
(2, 'This is paragraph 1.' + CHAR(10) + CHAR(13) + 'This is paragraph 2.'),
(3, 'This is paragraph 1.' + CHAR(13) + CHAR(10) + 'This is paragraph 2.')
-- 2 & 3 dont match, but they should.
-- For some reason a trailing space was added to Id 2 & 3
-- in the @delimiters table at the time of the insert clause.
SELECT
REPLACE(s.[str], d.delim, d.newDelim) AS NewStr
FROM @strings s
LEFT JOIN @delimiters d ON s.Id = d.Id
-- Adding a TRIM() fixes it
SELECT
REPLACE(s.[str], TRIM(d.delim), d.newDelim) AS NewStr
FROM @strings s
LEFT JOIN @delimiters d ON s.Id = d.Id
-- Clear the @delimiters table
DELETE FROM @delimiters WHERE delim IS NOT NULL
-- Now Im adding a TRIM() during the insert.
-- You can literally add it around any
-- single or combination of CHAR()s in the delim column.
INSERT INTO @delimiters
VALUES
(1, TRIM(CHAR(10)) + CHAR(32) + CHAR(13), '$'),
(2, CHAR(10) + CHAR(13), '#'),
(3, CHAR(13) + CHAR(10), '@')
-- Now they all match, I only put the TRIM()
-- on the first CHAR() for ID=1 during the insert,
-- but it trimmed the trailing space for all of them.
SELECT
REPLACE(s.[str], d.delim, d.newDelim) AS NewStr
FROM @strings s
LEFT JOIN @delimiters d ON s.Id = d.Id
Result set I get:
