Some time ago, I checked BOL to read about the PATINDEX string function, because I wanted to use a regular expression instead of CHARINDEX with a character string.

Unfortunately, the examples shown in BOL looked to me like CHARINDEX usage.

I didn't see comparison of when to use PATINDEX rather than CHARINDEX, except for a note that CHARINDEX cannot be used on text, ntext or image datatypes.

Digging deeper, I DID find the explanation of wildcards, including the use of brackets like [0-9] for "any number" or [^0-9] for "not a number".

I also learned that you could use these "regular expression"-type wildcards with CHARINDEX and LIKE, which is handy information. CHARINDEX has the added feature of being able to specify a starting position for the search.

As near as I could see, the only time you need to use PATINDEX is for text, ntext datatypes.

Does anyone know if there is an advantage to using PATINDEX?

(Furthermore, the only WHERE conditions that you can use on text columns are LIKE, IS NULL, and PATINDEX. Makes me wonder if PATINDEX was designed for use with text, ntext datatypes as opposed to char, nchar, varchar, etc.)