T-SQL "pattern"表达式字符类中的转义 ] 和 ^ 字符

Question

我试图模仿Oracle的RTRIM(expression, characters)在MsSql Server 2008 R2与以下查询:

REVERSE(
        SUBSTRING(
                  REVERSE(field),
                  PATINDEX('%[^chars]%', REVERSE(field)),
                  LEN(field) - PATINDEX('%[^chars]%', REVERSE(field)) + 1
             )
       )

问题是，我希望能够修剪像]和^这样的字符，这些字符可能需要转义。

我不知道该怎么做。像]这样的东西不起作用。

我知道ESCAPE子句，但我不明白它是如何工作的，顺便说一下，SqlServer拒绝它，如果把它放在模式字符串之后。

有趣的事实:

如果我写%[^^]%(希望修剪^)，它不起作用。

如果我写%[^ ^]%，它会修剪^，但显然也会修剪空格!

Answer 1

不好看，但是…

CREATE FUNCTION dbo.RTRIMCHARS(
    @input AS VARCHAR(MAX), @chars AS VARCHAR(100)
) RETURNS VARCHAR(MAX) 
AS 
BEGIN
    DECLARE @charpos BIGINT
    DECLARE @strpos BIGINT
    SET @strpos = LEN(@input)
    SET @charpos = LEN(@chars)
    IF @strpos IS NULL OR @charpos IS NULL RETURN NULL
    IF @strpos = 0 OR @charpos = 0 RETURN @input
    WHILE @strpos > 0
    BEGIN
        SET @charpos = LEN(@chars)
        WHILE @charpos > 0
        BEGIN
            IF SUBSTRING(@chars, @charpos, 1) = SUBSTRING(@input, @strpos, 1)
            BEGIN
                SET @strpos = @strpos - 1
                BREAK
            END
            ELSE
            BEGIN
                SET @charpos = @charpos - 1
            END
        END
        IF @charpos = 0 BREAK
    END
    RETURN SUBSTRING(@input, 1, @strpos)
END

使用

SELECT dbo.RTRIMCHARS('bla%123', '123%')   -- 'bla'
SELECT dbo.RTRIMCHARS('bla%123', '123')    -- 'bla%'
SELECT dbo.RTRIMCHARS('bla%123', 'xyz')    -- 'bla%123'
SELECT dbo.RTRIMCHARS('bla%123', ']')      -- 'bla%123'
SELECT dbo.RTRIMCHARS('bla%123', '')       -- 'bla%123'
SELECT dbo.RTRIMCHARS('bla%123', NULL)     -- NULL
SELECT dbo.RTRIMCHARS(NULL, '123')         -- NULL

Answer 2

我在MS Connect上找到了这个文档:
http://connect.microsoft.com/SQLServer/feedback/details/259534/patindex-missing-escape-clause

用户用PATINDEX询问ESCAPE子句，然后另一个用户也扩展了CHARINDEX的请求。

女士答:票关闭作为无法解决: (

我完成了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脚本…

---

编辑:

我为两个参数添加了NULL检查，以反映Oracle和Postgres的行为。

不幸的是，Oracle的行为仍然略有不同:
在你写LTRIM(string, '')的情况下，它返回NULL，因为0长度的字符串在Oracle中就像NULL，所以它实际上返回LTRIM(string, NULL)的结果，这是NULL。

顺便说一句，这真是一个奇怪的例子。