CODEWITHSUNDEEP
t-sql
jnm2
jnm2

Reputation: 8364

Is it possible to match any character that is not ']' in PATINDEX?

I need to find the index of the first character that is not ]. Normally to match any character except X, you use the pattern [^X]. The problem is that [^]] simply closes the first bracket too early. The first part, [^], will match any character.

In the documentation for the LIKE operator, if you scroll down to the section "Using Wildcard Characters As Literals" it shows a table of methods to indicated literal characters like [ and ] inside a pattern. It makes no mention of using [ or ] inside double brackets. If the pattern is being used with the LIKE operator, you would use the ESCAPE clause. LIKE doesn't return an index and PATINDEX doesn't seem to have a parameter for an escape clause.

Is there no way to do this?

(This may seem arbitrary. To put some context around it, I need to match ] immediately followed by a character that is not ] in order to locate the end of a quoted identifier. ]] is the only character escape inside a quoted identifier.)

Upvotes: 4

Views: 1741

Answers (1)

Martin Smith
Martin Smith

Reputation: 453847

This isn't possible. The Connect item PATINDEX Missing ESCAPE Clause is closed as won't fix.

I'd probably use CLR and regular expressions.

A simple implementation might be

using System.Data.SqlTypes;
using System.Text.RegularExpressions;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlInt32 PatIndexCLR(SqlString pattern, SqlString expression)
    {
        if (pattern.IsNull || expression.IsNull)
            return new SqlInt32();

        Match match = Regex.Match(expression.ToString(), pattern.ToString());
        if (match.Success)
        {
            return new SqlInt32(match.Index + 1);
        }
        else
        {
            return new SqlInt32(0);
        }
    }
}

With example usage

SELECT  [dbo].[PatIndexCLR] ( N'[^]]', N']]]]]]]]ABC[DEF');

If that is not an option a possible flaky workaround might be to substitute a character unlikely to be in the data without this special significance in the grammar.

WITH T(Value) AS
(
SELECT ']]]]]]]]ABC[DEF'
)
SELECT PATINDEX('%[^' + char(7) + ']%', REPLACE(Value,']', char(7)))
FROM T

(Returns 9)

Upvotes: 4

Related Questions