SQL Charindex(), Locate(), Instr() Functions

The SQL CHARINDEX() | LOCATE() | INSTR() is a function and returns the index position of the first occurrence of substring of a given input string or text.

The SQL CHARINDEX() use to find the numeric starting position of a search string inside another string.

The SQL CHARINDEX() function returns "0" if given substring does not exist in the input string.

The SQL CHARINDEX() function is supports or work with character and numeric based columns.

It can be used in any valid SQL SELECT statement as well in SQL where clause.

CASE statement with CHARINDEX. We can check whether a string or a character is present in another string. The character or word is present if the CHARINDEX function returns a non-zero index number. It wouldn't exist if it didn't.

Example 1: Here is the Example.

DECLARE @Name as VARCHAR(100) = 'Sha Ka Ba';

SELECT CASE WHEN CHARINDEX('Ka', @Name) > 0 THEN 'Exists'
            ELSE 'Not Exists'
       END as Test;

SELECT CASE WHEN CHARINDEX('Pk', @Name) > 0 THEN 'Exists'
            ELSE 'Not Exists'
       END as Test;

Example 2: When clauses only need to have these three requirements. To ensure that the > character is present in the string, use charindex:

CASE 
WHEN commodity IS NULL THEN 'No Comodity'
WHEN CHARINDEX('>', Commodity) > 0 THEN 
     SUBSTRING(commodity, CHARINDEX('>', commodity) + 2, LEN(commodity))
ELSE comodity
END

CHARINDEX makes it simple to find the first instance of a string in another string. It can also provide a simple technique of determining the final occurrence of a string by reversing the order of the string.

To identify the position of the last '/' character, CHARINDEX searches the reversed string. All characters to the right of this position are subsequently extracted using the RIGHT function.

For instance if a string contains the full path to a file, such as :

C:\Program Files\Microsoft SQL Server\MSSQL\DATA\AdventureWorks_Data.mdf

This technique can be used to extract just the filename (i.e. the part after the last '\') :

AdventureWorks_Data.mdf

I have column called assocname.

Sample data:

1. FirstParty>Individual:2
2. FirstParty:3>FPRep:2>Individual
3. Incident>Vehicle:2>RegisteredOwner>Individual3

I want to get the string before the last occurrence '>'. Here is the result:

1. FirstParty
2. FirstParty:3>FPRep:2
3. Incident>Vehicle:2>RegisteredOwner

Example 1: Using the RIGHT function, we can easily strip off just the filename by looking for the last occurrence of the delimiter ". That's exactly what the SQL below accomplishes:

DECLARE @FullPath VARCHAR(200)
SET @FullPath =
 'C:\Program Files\Microsoft SQL Server\MSSQL\DATA\AdventureWorks_Data.mdf'

SELECT RIGHT(@FullPath , CHARINDEX ('\' ,REVERSE(@FullPath))-1)

If we run this query it returns just the filename.

Example 2: The following query uses this technique to extract the file name from the full path name in the sys.master_files system view :

SELECT  physical_name
       ,RIGHT(physical_name,CHARINDEX('\',REVERSE(physical_name))-1) file_name
FROM    sys.master_files

Example 3: In the example given below, we need to search for the last occurrence of word ‘the’ in the sentence.

DECLARE @String AS VARCHAR(100)
DECLARE @Search_String AS VARCHAR(100)
 
SET @String ='The SQL SERVER is one of the best applications of Microsoft'
SET @Search_String='the'

Example 4: Find Last occurrence of any character/word in the string:

SELECT DATALENGTH(@String)-CHARINDEX(REVERSE(@Search_String)
,REVERSE(@String))-1 As [Last occurrence]

Output:

The CHARINDEX and PATINDEX functions are used to determine a pattern's starting location. Another distinction is that with CHARINDEX, the pattern to be searched is limited to 8000 bytes.

Similarity:

• Both routines take two arguments and search for the required text in the given expression.
• The initial location of the matching pattern specified in the function is returned by both functions.

Difference:

• With wildcard characters, the PatIndex function is utilised. The wildcard characters must be enclosed before (when searching last) or after (when searching first) the searching text.
• With the supplied searching pattern, the CharIndex function cannot utilise any wildcard characters. The wildcard characters will not be recognised by the CharIndex function.
• PATINDEX() allows you to search for patterns using wildcard characters. CHARINDEX(), on the other hand, does not.
• The third input to CHARINDEX() allows you to select the search's start location. PATINDEX(), on the other hand, does not.
• CHARINDEX() looks for one character expression within a second character expression and returns the first expression's starting position if it is found.
• PATINDEX() Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.

Syntax:

And here’s the official syntax of each function.

CHARINDEX()

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )

PATINDEX()

PATINDEX ( '%pattern%' , expression )

Both functions search for a character expression, however CHARINDEX() takes three arguments whereas PATINDEX() takes only two. The optional third input to the CHARINDEX() function allows you to set the search's beginning location. In other words, you can use the CHARINDEX() function to only return matches that occur after a specified point in the string.

Example 1: Example of PatIndex

SELECT (PATINDEX('%Corner%', 'C-SharpCorner'))

Result:

Example of CharIndex

SELECT (charindex('Corner', 'C-SharpCorner'))

Result:

Example 2: Here are examples that demonstrate the extra functionality provided by each function.

CHARINDEX()

Here, I add a third input to have a starting point for the search. In this example, it will skip the first occurrence of Bob and return the second occurrence's place.

SELECT CHARINDEX('Bob', 'Bob likes beer. Bob also likes beef.', 16);

Result:

So as mentioned, you can’t do this with PATINDEX() because it doesn’t accept that third parameter.

PATINDEX()

Now consider what you can accomplish with PATINDEX() that you can't do with CHARINDEX() (). In this case, we're looking for a pattern using wildcard characters:

SELECT PATINDEX('%b_er%', 'Bob likes beer.');

Result:

In this situation, we're using percent signs (%) as wildcards to indicate that any number of characters could come before or after our search phrase. We also employ the underscore (_), which is a wildcard character that can be used to represent any single character.

Conclusion: While both CHARINDEX() and PATINDEX() perform comparable tasks and may be used interchangeably in many situations, there are circumstances when you'll need to utilise one over the other.

Performance: The CHARINDEX AND PATINDEX came out to be equal in performance comparison. The two queries search for CREATE pattern in sys.sql_modules.definition column and both are equally good as per the execution plan.

This is readily performed using the SUBSTRING and CHARINDEX functions. We're fortunate in that the @ sign is distinct enough to be our "special character" to look for.

The CHARINDEX method "searches an expression for another expression and returns its starting point if found," while the SUBSTRING function returns a portion of a string.

The @ sign can be found using the charindex function, which is then used to extract the characters before the @ sign using the substring function.

The CHARINDEX function in SQL Server is used to locate the first or starting place of an expression or characters in a string. To put it another way, it's like looking for a specific character or letters in a string.

Syntax:

CHARINDEX (expression_to_find, expression_to_search, start_location)

The CHARINDEX function has three parameters by default. Optional is the third and final parameter, which is an Integer value.

Parameters:

1) expression_to_find

The first parameter is a character or a string of characters in which we wish to look for in another string.

Let's say we have a string called Light Years Away and we want to know where the word Years is in the string. As a result, the function's first parameter will be the word Years.

SELECT CHARINDEX('YEARS', 'Light Years Away') Location

This query returns a result of 7, indicating that the word Years begins at position seven in the above string. The function will discover the first location of the expression in a sentence like this, even if a word or character appears many times. Later occurrences will be overlooked.

2) expression_to_search:

A phrase or a sentence with letters such as the alphabet, numbers, and other special characters might be called an expression. A sentence with several characters is usually the second parameter in the function CHARINDEX. A variable or a column name can also be used as a parameter.

3) start_location:

The third parameter is an Integer value that is optional (you may or may not use it). You must put the figure if you want to locate or find an expression (expression_to_find) starting from a specific point in a string (expression_to_ search). If the third parameter is not specified in the function, the search begins at position 0.

Example 1: Here in our example, we have a list of categories separated by the symbol pipe |.

PHP | JAVA | PYTHON

The query to find the first position of the symbol “|” will be as follows.

SELECT CHARINDEX('|', 'PHP | JAVA | PYTHON') Location

Output:

Example 2: Use of start_location

We need to locate the position of the second pipe in the string because there are three categories separated by two pipes. To accomplish this, we must include a start_location after the first pipe, as the default search will begin at 0 and terminate with location 5.

SELECT CHARINDEX('|', 'PHP | JAVA | PYTHON', 6) Location

Output:

Example 3: Use of multiple CHARINDEX function in a single query.

Continuing with example 2, let us assume we do not have the start_location before hand an we have to find it dynamically.

SELECT CHARINDEX('|', 'PHP | JAVA | PYTHON', 
CHARINDEX('|', 'PHP | JAVA | PYTHON') + 1) Location

Or

WITH TAG AS (SELECT 'PHP | JAVA | PYTHON' AS Category)
SELECT CHARINDEX('|', Category, CHARINDEX('|', 'PHP | JAVA | PYTHON') + 1) Location
FROM TAG;

Output:

Example 4: Ok, we found locations of the pipe. Now, using the locations we need to extract a value from the above-mentioned string.

WITH TAG AS (SELECT 'PHP | JAVA | PYTHON' AS Category)
SELECT SUBSTRING(Category, 1, CHARINDEX('|', Category) - 1) Category
FROM TAG;

Example 5: Here's a quick technique to get (extract) all of the characters in a string before a special character. We have a list of email addresses in our instance, and we want to extract the name before the @domain.

Someone from the network team needs to gather all of the network usernames, which just so happen to be the same as their email address. By pulling only the name from the email address, we can assist them.

SELECT EmailAddress
FROM [dbo].[DimEmployee]

Example 6: The @ sign can be found using the charindex function, which is then used to extract the characters before the @ sign using the substring function. Although that is a mouthful, the query below demonstrates how simple it is.

SELECT EmailAddress
 ,SUBSTRING(emailaddress, 0, charindex('@', emailaddress, 0))
FROM [dbo].[DimEmployee]

You don't always have to construct your own or write procedural code to obtain the results you need in SQL Server because there are many strong built-in functions.

The CHARINDEX() function returns the position of the substring within the specified string. This works the opposite of the SUBSTRING function. Unlike the SUBSTRING() function, this function starts the search from a specified position and returns the position of the substring. If a substring is not found, it returns zero. The CHARINDEX() function is used to perform case-sensitive and case-insensitive searches based on the collation specified in the query.

Syntax:

Following is the syntax of the CHARDINDEX() function.

CHARINDEX(substring, input_string [, start_location])

CHARINDEX() accepts three arguments:

• Substring: This argument defines the substring which you need to go looking inside the enter string.
• Input_string: This argument defines the enter string.
• Start_location: This argument defines the vicinity from that you need to begin the quest inside the enter string. The information kind of this parameter is an integer, and that is an non-compulsory parameter. If this parameter isn't always specified, then the quest begins offevolved from the start of the enter string.

Example 1: Use CHARINDEX using literal string input

Check the position of the “example” substring from the input string “This is CHARINDEX example”. To do that, execute the following query:

SELECT Charindex('example', 'This is CHARINDEX example')AS Output;

Output:

Now, execute the following query:

SELECT Charindex('examples', 'This is CHARINDEX example')AS Output

Output:

Here output will not display because examples not available in the index.

Example 2: In the below example, we retrieve the position of substring simmanchith.com using the CHARINDEX. It returns the starting position of the substring as 16. In the earlier example of the SUBSTRING function, we specified the starting position 16 to returns the string.

For example, in the below query, we use the COLLATE function along with the collation latin_general_cs_as.

SELECT Charindex('sqlshack.com', 
'This is SQLSHACK.COM' COLLATE latin1_general_cs_as)
  AS Output;

It returns zero in the output because it considers sqlshack.com separate from SQLSHACK.COM.

Example 3: CHARINDEX function can also perform case-sensitive searches. We need to use the COLLATE() function for a case-sensitive search.

COLLATE() function: Let’s change the substring in the capital letter to match with the string.

SELECT Charindex ('SQLSHACK.COM', 
'This is SQLSHACK.COM' COLLATE latin1_general_cs_as)
AS Output;

It returns the position of SUBSTRING using the case-sensitive search using the CHARINDEX function.

Example 4: Here Add an optional starting position in the CHARINDEX() function. For example, in the below query, the 2nd query specifies a starting position at 8. Therefore, it starts looking for the substring from the 8th character position.

SELECT CHARINDEX('SQLSHACK', 'SQLSHACK - SQLSHACK.COM') 
SELECT CHARINDEX('SQLSHACK', 'SQLSHACK - SQLSHACK.COM',8)

CHARINDEX makes it simple to find the first instance of a string in another string. It can also provide a simple technique of determining the final appearance of a string by reversing the order of the string.

For instance if a string contains the full path to a file, such as:

C:\Program Files\Microsoft SQL Server\MSSQL\DATA\AdventureWorks_Data.mdf

This technique can be used to extract just the filename (i.e. the part after the last '\') :

AdventureWorks_Data.mdf

Example: By searching for the location of the last occurrence of the delimiter '\' we can easily strip off just the filename using the RIGHT function. The following SQL does just that:

DECLARE @FullPath VARCHAR(200)
SET @FullPath =
'C:\Program Files\Microsoft SQL Server\MSSQL\DATA\AdventureWorks_Data.mdf'

SELECT RIGHT(@FullPath , CHARINDEX ('\' ,REVERSE(@FullPath))-1)

If we run this query it returns just the filename.

Example 2: In this case, CHARINDEX is looking for the last '\' character in the inverted string. All characters to the right of this position are subsequently extracted using the RIGHT function.

The following query uses this technique to extract the file name from the full path name in the sys.master_files system view:

SELECT  physical_name
       ,RIGHT(physical_name,CHARINDEX('\',REVERSE(physical_name))-1) file_name
FROM    sys.master_files

As a DBA, you may be asked to extract the domain of an email address from a database table. You may count the number of extracted domains from Email in SQL Server if you want to count the most often used domain names from email addresses in any dataset.

Example 1: SQL Queries could be used to extract the domain from the Email address.

Let us created table named “email_demo”:

create table (ID int, Email varchar (200));

Inserting values in the table email_demo:

insert into email_demo values(
(1, 'Sam@gfg.com'), (2, 'Khushi@gfg.com'),
(3, 'Komal@gfg.org'), (4, 'Priya@xyz.com'),
(5, 'Sam@xyz.com'), (6, 'Krish@xyz.com'),
(7, 'Neha@gfg.com'), (8, 'Pam@gfg.com'),
(9, 'Mohan@abc.com'), (10, 'Ankit@mno.com'),
(11, 'Kumari@gfg.com'), (12, 'Hina@abc.com'),
(13, 'Jaya@mno.com'), (14, 'Piyush@abc.com'),
(15, 'Khushi@xyz.com'), (16, 'Mona@gfg.org'),
(17, 'Roza@abc.com'));

Displaying the table contents:

select * from email_demo;

Output:

Method 1: Extract Domain From Email in SQL Server :

In below example we will use SUBSTRING function to select the string after the @ symbol in each of the value.

SQL Extract Domain From Email:

SELECT ID,
SUBSTRING ([Email], CHARINDEX( '@', [Email]) + 1,
LEN([Email])) AS [Domain]
FROM [email_demo];

Output:

Approached used:

• In the SUBSTRING function, we allocated the Source to our Column Name 'Email.'
• Then, after finding the @ symbol with the CHARINDEX function, we added 1 to make the starting point after the @ symbol.
• Then, to define the end value, we used the LEN function.

Example 2: Count the number of extracted Domain From Email in SQL Server:

Approach 1: SQL Query to Count Number of Records for Extract Domain name From Email:

SELECT RIGHT ([Email],
LEN([Email]) - CHARINDEX( '@', [Email])) AS [Domain],
COUNT(*) AS [Total Number of Domain]
FROM [email_demo]
WHERE LEN([Email ]) > 0
GROUP BY RIGHT([Email],
LEN([Email]) - CHARINDEX( '@', [Email]));

Output:

Approach 2: SQL Query to Count Number of Records for Extract Domain name From Email:

SELECT SUBSTRING ([Email],
CHARINDEX( '@', [Email] ) + 1, LEN([Email])) AS [Domain],
COUNT(*) AS [Total Number of Domain]
FROM [email_demo]
WHERE LEN([Email]) > 1
GROUP BY SUBSTRING ([Email], CHARINDEX( '@', [Email] ) + 1,
LEN([Email]));

Output:

Example 3: SQL Extract Domain From Email Example:

The SUBSTRING function allows you to extract and display the part of a string. In this SQL Server example, we will show you how to Select string after the @ symbol in each record.

SELECT SUBSTRING (
	[Email Adress],
	CHARINDEX( '@', [Email Adress] ) + 1,
	LEN([Email Adress])
	) AS [Domain Name]
FROM [EmailAdress]

In general, the SUBSTRING Function allows three parameters, and they are Source, start point, endpoint. Here we assigned the Source as our Column Name:

Next, we used the CHARINDEX Function to find the @ symbol, and then we added 1. so that the starting point will be after the @ symbol.

Lastly, we used the LEN Function in SQL Server to specify the end value.

SUBSTRING (
	[Email Adress], -- Source
	CHARINDEX( '@', [Email Adress] ) + 1, -- Start Point
	LEN([Email Adress] -- End Point
)

Example 4: Extract Domain From Email Example

We show how to use the Right Function to extract the domain name from the email address.

SQL Query to Extract Domain name From Email and Count Number of Records

SELECT RIGHT (
[Email Adress],
LEN([Email Adress]) - CHARINDEX( '@', [Email Adress] )
 ) AS [Domain Name],
COUNT(*) AS [Total Records with this Domain]
FROM [EmailAdress]
WHERE LEN([Email Adress]) > 0
GROUP BY RIGHT (
[Email Adress],
LEN([Email Adress]) - CHARINDEX( '@', [Email Adress] )
)

Example 5: Telephone numbers have a fixed structure, but email addresses are a bit more tricky to parse since you don't know their exact length upfront. An email address has the following format:

< recipient>@domain

Where domain = < domain name>.< top-level domain>

In this example, we're assuming there's only one @ symbol present in the email address. Technically, you can have multiple @ symbols, where the last one is the delimiter between the recipient and the domain. This is for example a valid email address: "user@company"@company.com. This is out of scope for this tip.

Using the CHARINDEX function, we can find the position of the @. The recipient can then be found by taking the start of the email address right until the @ symbol. The domain is everything that comes after the @ symbol. If you also want to extract the top-level domain, you cannot search for the first dot starting from the right, since some top-level domains have a dot, for example co.uk. Let's see how we can parse the email address john.doe@mssqltips.co.uk.

WITH cte_sample AS
(
    SELECT email = 'john.doe@mssqltips.co.uk'
)
SELECT
 email
 ,recipient  = SUBSTRING(email,1,CHARINDEX('@',email,1) - 1)
 ,fulldomain = SUBSTRING(email,CHARINDEX('@',email,1) + 1,LEN(email))
 ,domainname = SUBSTRING( email
 ,CHARINDEX('@',email,1) + 1 -- start is one char after the @
 , -- starting position of charindex is the position of @
 CHARINDEX('.',email,CHARINDEX('@',email,1))  
 - CHARINDEX('@',email,1)
-- length is the position of the first dot after the @ - position of the @
 )
,toplevel   = SUBSTRING( email
 ,CHARINDEX('.',email,CHARINDEX('@',email,1)) + 1 -- position of first dot after @
 ,LEN(email)
)
FROM [cte_sample];

We provided LEN(email) for both the complete domain and the top-level domain, which is too long. If the SUBSTRING length is more than the number of characters, everything is returned until the last character. This saves us from having to write a more difficult expression to calculate the correct length when all we need is the substring from a specific location to the end of the text.

The CHARINDEX() function returns the location of a substring found in a string.

This function returns 0 if the substring cannot be found.

Note that this function searches without regard for case.

Syntax:

CHARINDEX(substring, string, start)

Parameter Values

Example 1: Search for "OM" in string "Customer", and return position:

SELECT CHARINDEX('OM', 'Customer') AS MatchPosition;

Example 2: You can explicitly perform a case-sensitive search by adding the COLLATE clause to your SELECT statement:

Here’s an example that includes a case-sensitive search and a case-insensitive search:

SELECT 
  CHARINDEX('Beer', 'Bob likes beer.' COLLATE Latin1_General_CS_AS) AS 'Case-Sensitive',
  CHARINDEX('Beer', 'Bob likes beer.' COLLATE Latin1_General_CI_AS) AS 'Case-Insensitive';

Result:

The first one is case-sensitive because _CS (in the collation) stands for Case-Sensitive. The second one is case-insensitive because _CI stands for Case-Insensitive.

Example 3: This statement shows a case-insensitive search for the string 'SERVER' in 'SQL Server CHARINDEX':

SELECT CHARINDEX(
   'SERVER', 
   'SQL Server CHARINDEX'
) position;

Output:

Example 1: The CHARINDEX() method in SQL is useful for extracting characters from a string. However, it only returns the first time a character appears. It's common to need to find the Nth instance of a character or a space, which can be a difficult process in traditional SQL. This is possible thanks to a method I created called CHARINDEX2(). The target string or character, the string to be searched, and the occurrence of the target string/character are all passed in as parameters.

SELECT dbo.CHARINDEX2('a', 'abbabba', 3)

Returns the location of the third occurrence of 'a'

CREATE FUNCTION CHARINDEX2
(
@TargetStr varchar(8000), 
@SearchedStr varchar(8000), 
@Occurrence int
)

RETURNS int

as
begin

declare @pos int, @counter int, @ret int

set @pos = CHARINDEX(@TargetStr, @SearchedStr)
set @counter = 1

if @Occurrence = 1 set @ret = @pos

else
begin

while (@counter < @Occurrence)
begin

select @ret = CHARINDEX(@TargetStr, @SearchedStr, @pos + 1)

set @counter = @counter + 1

set @pos = @ret

end

end

RETURN(@ret)

end

Example 2: After specifying the number of occurrences (nth) of the same character, this function returns the position or index of characters in a string:

CREATE FUNCTION CharIndexAfterN (
   @Char VARCHAR(10)
   ,@String VARCHAR(MAX)
   ,@Occurance INT
   )
RETURNS INT
AS
BEGIN
  DECLARE @Index AS INT = 1

  WHILE @Occurance <> 0
  BEGIN
SET @Index = CHARINDEX(@Char, @String, @Index + 1)
SET @Occurance -= 1
  END

RETURN @Index
END

For example:

SELECT dbo.CharIndexAfterN('ab', '***ab****ab*******ab*', 2)

Output:

Example 3: I've got a string and I'd like to get the text between two letters. However, the letters exist multiple times in the string, and I want to return the text between the Nth and Nth appearances of the character.

In this instance, I have a table of MDX members and I'd like to get the member's code. The text between the final (fourth) square brackets is the code.

Set up some testing data:

CREATE TABLE #T
([TestColumn] nvarchar(200) NOT NULL);

INSERT INTO #T
([TestColumn])
VALUES
('[Countries Regions Cities].[Countries].[Country].&[CN]'),
('[Countries Regions Cities].[Countries].[Region].&[4]');

I have two solutions. One uses a recursive CTE and the other uses CROSS APPLY.

APPROACH 1:

Using Recursive CTE: The occurrence variable sets the Nth term I want to return.

DECLARE @start_character nvarchar(1);
DECLARE @end_character nvarchar(1);
DECLARE @occurence int;

SET @start_character = '[';
SET @end_character = ']';
SET @occurence = 4;

WITH T AS 
(
SELECT 1 AS [Occ],
charindex(@start_character, [TestColumn]) AS pos1, 
charindex(@end_character, [TestColumn]) AS pos2, 
[TestColumn] 
FROM #T

UNION ALL

SELECT [Occ] + 1 AS [Occ], 
charindex(@start_character, [TestColumn], pos1 + 1) AS pos1, 
charindex(@end_character, [TestColumn], pos2 + 1) AS pos2,
[TestColumn]
FROM T
WHERE pos1 > 0
)

SELECT [Occ],
[TestColumn], 
substring([TestColumn], pos1 + 1, pos2 - pos1 - 1) AS [Text]
FROM T 
WHERE [Occ] = @occurence;

APPROACH 2:

Using CROSS APPLY

This actually returns the hierarchy name, but to return the code you just have to add another APPLY that looks for the third position:

DECLARE @character1 nvarchar(1);
DECLARE @character2 nvarchar(1);

SET @character1 = '[';
SET @character2 = ']';

SELECT [TestColumn],
substring([TestColumn], P3.Pos + 1, C3.Pos - P3.Pos - 1) AS [Text]
FROM #T
    CROSS APPLY (SELECT (charindex(@character1, [TestColumn]))) AS P1(Pos)
    CROSS APPLY (SELECT (charindex(@character2, [TestColumn]))) AS C1(Pos)
    CROSS APPLY (SELECT (charindex(@character1, [TestColumn], P1.Pos+1))) AS P2(Pos)
    CROSS APPLY (SELECT (charindex(@character2, [TestColumn], C1.Pos+1))) AS C2(Pos)
    CROSS APPLY (SELECT (charindex(@character1, [TestColumn], P2.Pos+1))) AS P3(Pos)
    CROSS APPLY (SELECT (charindex(@character2, [TestColumn], C2.Pos+1))) AS C3(Pos);

The efficiency of the two solutions does not appear to differ significantly. The Recursive CTE can readily expand to enormous strings with a large number of recurrences of a character (i.e. N is vast), whereas the APPLY technique would become extremely verbose. The APPLY method, on the other hand, is well suited to returning more data from the same row (i.e. I could return both the 3rd and 4th occurrences on the same row).