CODEWITHSUNDEEP
sqlsql-servert-sql
Seafish
Seafish

Reputation: 2271

Significance of double percent signs (%%) in T-SQL PATINDEX

I ran across a legacy piece of T-SQL that uses a PATINDEX similar to the following to extract a date out of a text column:

CAST(SUBSTRING(MyText, PATINDEX('%%-[0-1][0-9]/[0-3][0-9]/[0-9][0-9]%%', DocumentHeaderText)+1, 8) AS DATE)

I can't see the reason for the double percent signs at the beginning and end of the wildcard string, and from Googling around and looking at the PATINDEX documentation it doesn't seem like a double percent sign does anything more than a single percent sign.

However, I've been bitten before by hastily "improving" legacy code, finding out the hard way that the original author had a good reason for what they did and having to change it back.

So my question is, is there any difference between %% and % in a wildcard string for a T-SQL LIKE or PATINDEX statement? Can I safely change the code without altering the behavior to:

CAST(SUBSTRING(MyText, PATINDEX('%-[0-1][0-9]/[0-3][0-9]/[0-9][0-9]%', DocumentHeaderText)+1, 8) AS DATE)

Upvotes: 0

Views: 4158

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82524

The official documentation of PATINDEX states that:

pattern
Is a character expression that contains the sequence to be found. Wildcard characters can be used; however, the % character must come before and follow pattern (except when you search for first or last characters)

The % wildcard stands for, as written in the official documentation for LIKE:

any string of zero or more characters.

The fact that it can stand for any number of characters including zero, means that %% is completely equivalent to % and there for can be safely be changed.

Please note that this is not the case with any other T-SQL wildcards - since _ stands for a single char, as well as [] and [^].

Upvotes: 3

Related Questions