PATINDEX returning 0 on matching rexpresson

问题:

As far as I found out, T-SQL has a possibility to check for regular expressions, via PATINDEX.

My task is simple: I get a telecom-Type (phone for example). On a mapping table, a regex to validate this type is saved. So before saving, I'd like to check this regex.

So easy done:

  -- Check the regular expression for phone 
  DECLARE  @regExp  nvarchar(255);

  SELECT   @regExp = tt.ValidationRegex
  FROM     Core.TelecomType tt
  WHERE    tt.Code =  @DEFAULT_PHONE_TYPE;

Sadly, this always returns 0, even with wildcards with tries like this:

  SET @regExp = CONCAT('%', @regExp, '%');
  SET @regExp = CONCAT(@regExp, '%');
  SET @regExp = CONCAT('%', @regExp );

On RegExr and oracle-side, the values seem to match, so is this a problem on the T-SQL? If yes, is there a workarround for this?

Thanks in advance

Matthias

回答1:

Nope, sorry. PATINDEX doesn't let you match a regular expression, it works with the same kinds of patterns that are used with LIKE.

A quote from the documentation:

PATINDEX works just like LIKE, so you can use any of the wildcards. You do not have to enclose the pattern between percents. PATINDEX('a%', 'abc') returns 1 and PATINDEX('%a', 'cba') returns 3. Unlike LIKE, PATINDEX returns a position, similar to what CHARINDEX does.

If you need regular expression matching in T-SQL, you'll have to rely on a custom CLR function/procedure. You can also check this to see if you can use it.