Last time, we learned about CHARINDEX and all of the fun that it can do to make dealing with strings within strings a bit easier. This time, I want to talk about another function that does kind of the same thing but slightly differently.
The function I’m talking about is PATINDEX. When you think about PATINDEX, you should be thinking PATterns. Yes, that’s right my friends – patterns.
Using the CHARINDEX function, we were able to find those venues that had the “Theatre” substring as well as the position it was in.
With the PATINDEX, we can find those Venues that have “Theatre” as the last word in the Venue string!
Notice there is a difference here in what’s typed – beyond the obvious change from CHARINDEX to PATINDEX.
- There is now a “%” character before Theatre – this tells SQL Server to allow for any character before the phrase “Theatre.” If there had been one after, then it would allow for any character after the phrase. Since the ask was to see if the phrase was at the end of the string, then we only need to put the % in front of the phrase.
- If you’ll compare the 2 pictures above, you’ll see there is now only 13 rows returned instead of the 14 rows returned by CHARINDEX.
Now, only CHARINDEX allows you to look for a substring after a specified position. And only PATINDEX lets you look for patterns in the substrings.
But, for the majority of the work that you’ll do with these two functions, feel free to use whichever you like more. They both work exactly the same – just remember to put a “%” on both sides of the substring for the PATINDEX while leaving the CHARINDEX alone.
Next time, we’ll go over another way to get strings within substrings. Until then my friends!