Continuing on with our series from last time – see here if you somehow missed it – let’s have some more fun with the different functions we can use with strings.
This time, let’s focus on looking for different items we can use to find a string within a string.
One of my favorite functions is CHARINDEX for this functionality.
CHARINDEX returns the position, or index, of the first character of where one string exists within the other.
For example, let’s work with one of our favorite data sets – Nirvana Performances – to learn more about this wonderful function.
Our use case is to find all of the times that Nirvana performed in a venue that had the British spelling on theater, aka “theatre”, in its name. To that, we will do as follows:
Now, yes, you could use LIKE in the WHERE clause to find the same bits of data. But could it tell you where it is? Hint: The answer is no.
But – even more importantly – the LIKE operator cannot look for the second use of a string to tell you where the string is like the CHARINDEX function can!
This time, we’re looking for the 2nd time that the phrase “er” shows up in the Venue name.
The way that I have done it above grabs the position of the first time the phrase shows in the string in the CTE. Then it uses that data to look for it a second time with the values found in the CTE.
There are 2 very important things to pay attention to here:
- The position that you want SQL to look at is the 3rd argument in the CHARINDEX function.
- Remember to put a + 1 after the Position found in the CTE when using it the second time. If you don’t, you’ll just found the first time that it finds the string again – which is definitely no bueno.
And, if you haven’t figured it out by now, also please note that if the string you’re searching for isn’t in the larger string, then CHARINDEX will return a 0 – hence why I included the >= 1 in the original query so that SQL will only return the ones that have the string in it.
And that, my friends, is all we have time for this week. Tune in next week, when we pontificate about other ways we can do this, but differently.
1 comment