Saturday, February 2, 2013

Wildcard based character search using PATINDEX

This is a follow up of the below blog on wildcard character based search

Extending this further, I did some experiments on how various wildcard characters will work inside pattern in t-sql .
Lets see how we can apply similar logic for getting positions of the first occurance of any of the wildcard characters individually rather than being a complete pattern within a string this time. Obviously this requires the usage of PATINDEX function and that's were things get a little more tricky for us.

PATINDEX does not support ESCAPE clause currently and this has been already raised as a required functionality by means of the connect item below.

This will make it a bit tricky as we cant escape all these wildcard characters when used inside PATINDEX by defining any escape character.
The only possible way I've found is as shown below by enclosing them within [] as per method 1 in earlier blog.
See this illustration below
SELECT *,PATINDEX('%[[_^%]%', string_value) AS Position,
COALESCE(NULLIF(patindex ('%[[^_%]%', string_value) ,0),patindex ('%]%', string_value)) AS [Positionwith[]
SELECT 'wrwrwewe%anjhd' AS string_value UNION ALL
SELECT 'jkhjkhnb[ ]m,n,m' UNION ALL
SELECT 'hjjhg_jhg^' UNION ALL
SELECT 'jhgjg$yutuu' UNION ALL
SELECT 'jhkh[vjhv' UNION ALL
SELECT 'jkhkh]h'

string_value         Position Positionwith[
wrwrwewe%anjhd         9         9
jkhjkhnb[ ]m,n,m 9         9
jkhjkhnb         0         0
hjjhg_jhg^         6         6
jhgjg$yutuu         0         0
jhkh[vjhv         5         5
jkhkh]h                 0         6

But the catch here is that moment you add ] character also as an item inside the pattern set it will be interpreted as the end of pattern and hence you'll not get expected results. I've not found a solution to this other than the work around applied above by keeping it within a separate PATINDEX
If anybody has found any cool way of integrating this within single PATINDEX I would love to see it. Also vote for the connect item if you can so that we can expect the enhancement to be provided soon for PATINDEX.