您可以使用以下方法实现此操作:
LEFT(Numbers, LEN(Numbers) - (PATINDEX('%[^,]%', REVERSE(Numbers)) - 1))
首先,需要使用REVERSE
函数将字符串反转:
REVERSE(Numbers) --> ,,,,,,3,2,1
然后使用PATINDEX
函数和模式匹配[^,]
查找第一个不是逗号的字符的位置:
PATINDEX('%[^,]%', REVERSE(Numbers)) --> ,,,,,,3,2,1 = 7
然后您可以使用
LEN
函数获取字符串的长度,以获得反向位置,即如果第一个不是逗号的字符在反转后的字符串中的位置为7,并且字符串的长度为10,则需要字符串的前4个字符。 然后使用
SUBSTRING
函数提取相关部分。
完整示例如下:
SELECT Numbers,
Reversed = REVERSE(Numbers),
Position = PATINDEX('%[^,]%', REVERSE(Numbers)),
TrimEnd = LEFT(Numbers, LEN(Numbers) - (PATINDEX('%[^,]%', REVERSE(Numbers)) - 1))
FROM (VALUES
('1,2,3'),
('1,2,3,'),
('1,2,3,,,'),
('1,2,3,,,,,,'),
('1,2,3,,,5,,,'),
(',,1,2,3,,,5,,')
) t (Numbers);
编辑
针对一次编辑,其中语法有些错误,下面是修剪开头和修剪逗号两侧的函数:
SELECT Numbers,
Reversed = REVERSE(Numbers),
Position = PATINDEX('%[^,]%', REVERSE(Numbers)),
TrimEnd = LEFT(Numbers, LEN(Numbers) - (PATINDEX('%[^,]%', REVERSE(Numbers)) - 1)),
TrimStart = SUBSTRING(Numbers, PATINDEX('%[^,]%', Numbers), LEN(Numbers)),
TrimBothSide = SUBSTRING(Numbers,
PATINDEX('%[^,]%', Numbers),
LEN(Numbers) -
(PATINDEX('%[^,]%', REVERSE(Numbers)) - 1) -
(PATINDEX('%[^,]%', Numbers) - 1)
)
FROM (VALUES
('1,2,3'),
('1,2,3,'),
('1,2,3,,,'),
('1,2,3,,,,,,'),
('1,2,3,,,5,,,'),
(',,1,2,3,,,5,,')
) t (Numbers);