Escaping ] and ^ characters in a T-SQL "pattern" expression character class

Question

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!

The problem is that the `]` closes the character class. In RegExp you use `\ ` to escape a `]` in a char class. — Teejay, Mar 08 '13 at 09:54

Tomalak · Answer 1 · 2013-03-08T17:58:45.817

1

Not pretty, but...

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

Usage

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

edited Mar 08 '13 at 17:58

answered Mar 08 '13 at 11:03

Tomalak

332,285
67
532
628

Thanks for your answer, but I finished writing my own one. Check it out :) – Teejay Mar 08 '13 at 12:07
Your function seems not to work fine: If I call `RTRIMCHARS('[PROJECT]', 'E')` it returns `[PROJ` – Teejay Mar 08 '13 at 12:19
@Teejay Should it not cut the input at the first occurrence of `E`? What would be the output on Oracle? – Tomalak Mar 08 '13 at 12:23
On EVERY coding language that implements LTrim/RTrim, they work like following: `LTRIM("eeeesomestring", "e") = "somestring"` and `RTRIM("somestringnnn", "n") = "somestring"` – Teejay Mar 08 '13 at 12:30
I mean it should cut all trimchars until the first non-trimchar is found. So, in `RTRIMCHARS('[PROJECT]', 'E')`, since the first char at right is a non-trimchar, the function should return the entire string `[PROJECT]` – Teejay Mar 08 '13 at 13:21
1

@Teejay I've fixed my code to work according to spec. Alas, it uses a nested loop as well, now. ;-) I'll vote your's up for coming up with your own solution; I just did not want to have broken code sit in one of my answers. If you want, please make a benchmark against your function, I'd be very interested in your findings! – Tomalak Mar 08 '13 at 18:01
Yes, yours is an R-Trim one... Needing both L and R trims, I decided to wrote the simpler one (L) and then use it to also implement the other (R). – Teejay Mar 10 '13 at 05:04
1

I found out that a native `LTRIM(str)` (like mine) or a native `RTRIM(str)` (like yours) runs about 20% faster than a `REVERSE(LTRIM(REVERSE(str)))`, though the latter gains in code-maintainability, since you've to edit only one function. – Teejay Mar 11 '13 at 11:13
1

As a side note, I implemented the same function in PLSQL (Oracle) and it responds within times similar to the stock `LTRIM`. Basically, even the native function needs to do the same operations. – Teejay Mar 11 '13 at 14:49

Teejay · Accepted Answer · 2013-03-11T12:12:31.253

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.

Going through the input character by character in a `WHILE` loop (and a nested one at that!) might be less efficient than my proposal. — Tomalak, Mar 08 '13 at 12:08
Normally, I'll trim 1-2 characters, so a performance degradation should be an issue. Anyway, I'll check your function out better! Thanks — Teejay, Mar 08 '13 at 12:12
Also note that my solution supports character ranges as in `a-z` or `0-9`. This could come in handy. — Tomalak, Mar 08 '13 at 12:14
Yes, I see. But it has some basic issue... See my comment on your answer — Teejay, Mar 08 '13 at 12:24
@Tomalak Also, I appreciate your effort to support ranges, but I actually need a clone of the Oracle function. We rely on a DB-indipendence layer that builds DDLs & DMLs for *Oracle* / *PgSql* / *MsSql*. — Teejay, Mar 08 '13 at 16:29
Yes, I understand that. In fact range support is a side-effect of me using `PATINDEX`, so it was not really intentional. — Tomalak, Mar 08 '13 at 17:06

Escaping ] and ^ characters in a T-SQL "pattern" expression character class

2 Answers2

Linked