转义]和^字符在T-SQL“模式”表达字符类 [英] Escaping ] and ^ characters in a T-SQL "pattern" expression character class
问题描述
$ b $我试图在MsSql Server 2008 R2中模拟Oracle的
RTRIM（表达式，字符） b pre> REVERSE（
SUBSTRING（
REVERSE（field），
PATINDEX（'％[^ chars]％'，REVERSE（field） ，
LEN（field） - PATINDEX（'％[^ chars]％'，REVERSE（field））+ 1
）
）
/ pre> 问题是我想要修剪像] 和 ^ 可能需要转义。
我不知道该怎么做。像 \] 的事情不起作用。
我知道 ESCAPE 子句，但我不明白它是如何工作的，顺便说一下，SqlServer拒绝它，如果放在模式字符串后。
有趣的事实：
如果我写％[^^]％（希望修剪 ^ ）它不工作。
如果我写％[^ ^]％它修剪 ^ ，但也明确地修剪空格！
我在MS Connect上找到这个文件：
http://connect.microsoft.com/SQLServer/feedback/details/259534/patindex-missing-escape-clause
用户询问关于 ESCAPE 子句与 PATINDEX ，则另一个用户扩展了 CHARINDEX 。
MS答案： Ticket cl ：（
我完成了自己的自定义功能 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或@TrimChars IS NULL
BEGIN
返回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
如果SUBSTRING（@BaseString，@BaseStrin gPos，1）= SUBSTRING（@TrimChars，@TrimCharsPos，1）
BEGIN
SET @TrimCharFound = 1
BREAK
END
SET @TrimCharsPos = @TrimCharsPos + 1
END
IF @TrimCharFound = 0
BEGIN
返回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）
）
返回varchar（2000）AS
BEGIN
RETURN REVERSE（LTrim_Chars（REVERSE（@BaseString），@TrimChars））
END
至少我学到了一些MsSql脚本...
编辑
我添加了 NULL 检查两个参数，以反映Oracle和Postgres的行为。
不幸的是，Oracle仍然表现略有不同：
在您写入 LTRIM（string，''）的情况下，它返回 NULL ，因为Oracle中的0长度字符串类似于 NULL ，所以它实际上是返回 LTRIM（string，NULL），这是 NULL 。
BTW这是一个非常奇怪的情况。
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.
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.
Fun fact:
If I write %[^^]% (desiring to trim ^) it doesn't work.
If I write %[^ ^]% it does trim ^, but clearly also trim spaces!
解决方案 I found this document on MS Connect:
http://connect.microsoft.com/SQLServer/feedback/details/259534/patindex-missing-escape-clause
The user asks about ESCAPE clause with PATINDEX, then another user extends the request for CHARINDEX as well.
MS answer: Ticket closed as Won't fix :(
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
And for RTrim:
CREATE FUNCTION RTrim_Chars (
@BaseString varchar(2000),
@TrimChars varchar(100)
)
RETURNS varchar(2000) AS
BEGIN
RETURN REVERSE(LTrim_Chars(REVERSE(@BaseString), @TrimChars))
END
At least, I learnt some MsSql scripting...
EDIT:
I added NULL checks for the two arguments, to reflect Oracle and Postgres' behavior.
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“模式”表达字符类的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
