Learning to use PATINDEX, but getting unexpected matches

Transact-SQL
1

I am trying use PATINDEX, but this simple example returns a match where I would not expect one. What am I missing, please?

SELECT PATINDEX('%[olzzzz]%', 'W3Schoolscom') position;
position
6

I would expect 0 since the pattern 'olzzzz' doesn't exist in the string. I get the same result from both SQL Server Management Studio on my laptop and from w3schools Website tutorial.

2022-11-17_11-30-45

2

Using the square brackets matches any of the characters within the brackets so in this case the first match is o. If you want to find the string olzzzz then use '%olzzzz%'.

1 Like
3

Amazing, what a simple mistake, thank you. The thing I am trying to work out is to extract a date from a string.

There is a learn.Microsoft page titles "Search Text with Regular Expressions" (can't post the link). Is this a reasonable guide to use for syntax?

Based on that, I was expecting to be able to use | (pipe) as an 'or' operator, but again I am falling short.

SELECT 
  PATINDEX('%[0-3][0-9]%', 'ABC_Sep_Report_6135082_02-Sep-19.pdf') Matches_too_early
 ,PATINDEX('%[0-3][0-9]-(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)-[0-9][0-9]%', 'ABC_Sep_Report_6135082_02-Sep-19.pdf') Test1
 ,PATINDEX('%(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)%', 'ABC_Sep_Report_6135082_02-Sep-19.pdf') Find_month
;
Matches_too_early Test1 Find_month
17 0 0
4

Pattern matching in SQL Server is rather limited. As an underscore represents any character maybe:

SELECT 
  PATINDEX('%[0-3][0-9]-%', 'ABC_Sep_Report_6135082_02-Sep-19.pdf') Matches_too_early
 ,PATINDEX('%[0-3][0-9]-___-[0-9][0-9]%', 'ABC_Sep_Report_6135082_02-Sep-19.pdf') Test1
 ,PATINDEX('%-___-%', 'ABC_Sep_Report_6135082_02-Sep-19.pdf') + 1 Find_month
;

You could also look at some CLR code to do regular expressions. eg:

https://sqlsharp.com/

1 Like