在 T-SQL“模式"中转义 ] 和 ^ 字符表达式字符类 [英] Escaping ] and ^ characters in a T-SQL "pattern" expression character class

问题描述

我正在尝试使用以下查询在 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“模式"中转义 ] 和 ^ 字符表达式字符类的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！