问题描述
我正在尝试使用以下查询在 MsSql Server 2008 R2 中模拟 Oracle 的 RTRIM(expression, characters)
:
I'm trying to emulate Oracle's RTRIM(expression, characters)
in MsSql Server 2008 R2 with the following query:
REVERSE(
SUBSTRING(
REVERSE(field),
PATINDEX('%[^chars]%', REVERSE(field)),
LEN(field) - PATINDEX('%[^chars]%', REVERSE(field)) + 1
)
)
问题是我希望能够修剪可能需要转义的 ]
和 ^
等字符.
The problem is that I want to be able to trim characters like ]
and ^
which do probably need escaping.
我不知道该怎么做.]
之类的东西不起作用.
I don't know how to do this. Things like ]
don't work.
我知道 ESCAPE
子句,但我不明白它是如何工作的,顺便说一下,如果放在模式字符串之后,SqlServer 会拒绝它.
I'm aware of the ESCAPE
clause but I do not understand exactly how it works and, by the way, SqlServer refuses it if put right after the pattern string.
有趣的事实:
如果我写 %[^^]%
(希望修剪 ^
)它不起作用.
If I write %[^^]%
(desiring to trim ^
) it doesn't work.
如果我写 %[^ ^]%
它确实会修剪 ^
,但显然也会修剪空格!
If I write %[^ ^]%
it does trim ^
, but clearly also trim spaces!
推荐答案
我在 MS Connect 上找到了这个文档:
http://connect.microsoft.com/SQLServer/feedback/details/259534/patindex-missing-escape-clause
I found this document on MS Connect:
http://connect.microsoft.com/SQLServer/feedback/details/259534/patindex-missing-escape-clause
用户用PATINDEX
询问ESCAPE
子句,然后另一个用户也扩展了CHARINDEX
的请求.
The user asks about ESCAPE
clause with PATINDEX
, then another user extends the request for CHARINDEX
as well.
MS 回答:Ticket 已关闭,因为 无法修复 :(
MS answer: Ticket closed as Won't fix :(
我完成了为 LTrim
编写自己的自定义函数:
I finished writing my own custom function for LTrim
:
CREATE FUNCTION LTrim_Chars (
@BaseString varchar(2000),
@TrimChars varchar(100)
)
RETURNS varchar(2000) AS
BEGIN
DECLARE @TrimCharFound bit
DECLARE @BaseStringPos int
DECLARE @TrimCharsPos int
DECLARE @BaseStringLen int
DECLARE @TrimCharsLen int
IF @BaseString IS NULL OR @TrimChars IS NULL
BEGIN
RETURN NULL
END
SET @BaseStringPos = 1
SET @BaseStringLen = LEN(@BaseString)
SET @TrimCharsLen = LEN(@TrimChars)
WHILE @BaseStringPos <= @BaseStringLen
BEGIN
SET @TrimCharFound = 0
SET @TrimCharsPos = 1
WHILE @TrimCharsPos <= @TrimCharsLen
BEGIN
IF SUBSTRING(@BaseString, @BaseStringPos, 1) = SUBSTRING(@TrimChars, @TrimCharsPos, 1)
BEGIN
SET @TrimCharFound = 1
BREAK
END
SET @TrimCharsPos = @TrimCharsPos + 1
END
IF @TrimCharFound = 0
BEGIN
RETURN SUBSTRING(@BaseString, @BaseStringPos, @BaseStringLen - @BaseStringPos + 1)
END
SET @BaseStringPos = @BaseStringPos + 1
END
RETURN ''
END
对于RTrim
:
CREATE FUNCTION RTrim_Chars (
@BaseString varchar(2000),
@TrimChars varchar(100)
)
RETURNS varchar(2000) AS
BEGIN
RETURN REVERSE(LTrim_Chars(REVERSE(@BaseString), @TrimChars))
END
至少,我学会了一些MsSql脚本……
At least, I learnt some MsSql scripting...
我添加了 NULL
检查这两个参数,以反映 Oracle 和 Postgres 的行为.
I added NULL
checks for the two arguments, to reflect Oracle and Postgres' behavior.
不幸的是,Oracle 的行为仍然略有不同:
在你写 LTRIM(string, '')
的情况下,它返回 NULL
,因为 0 长度的字符串就像 Oracle 中的 NULL
,所以它实际上是返回LTRIM(string, NULL)
的结果,确实是NULL
.
Unfortunately, Oracle still behaves slightly differently:
in the case you write LTRIM(string, '')
, it returns NULL
, since a 0-length string is like NULL
in Oracle, so it's actually returning the result of LTRIM(string, NULL)
, which is NULL
indeed.
顺便说一句,这是一个非常奇怪的案例.
BTW, this is a really strange case.
这篇关于在 T-SQL“模式"中转义 ] 和 ^ 字符表达式字符类的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!