SQL Find Nth Occurrence of Char in a String

 in SQL Server  tagged character / char / string / parse / charindex / nth / occurrence by

T-SQL’s CHARINDEX() function is useful for parsing out characters within a string. However, it only returns the first occurrence of a character. Over at SQL Server Central, there is a function that Cade Bryant wrote that returns the location of the Nth occurrence of a character. Below is the code from that article, formatted a bit differently. You can easily copy the code if you mouse-over the code and click on the <> icon in the top right corner (view source) to open the window that you can copy from with Ctrl+A and Ctrl+C (in Windows). Test this in a non-production server before trying it in a production server. 

/*
Example:
SELECT dbo.CHARINDEX2('a', 'abbabba', 3)
returns the location of the third occurrence of 'a'
which is 7
*/
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

If you copy this above code and run it in a query with a database open you will find that it created a new scalar-valued function under the Functions folder under the Programmability folder under the database you had open when you ran the script, in SQL Server Management Studio (SSMS). Don’t forget to refresh. To change the code you can right-click the name of the function, and click Modify. That creates ALTER FUNCTION code. Make any changes and Execute the query to save the changes.

You can run and test this code in a query without touching the tables in your database. Just open a new query and paste the SELECT code above into the query and execute the query. You will specify the string itself and which occurrence you want. In the first example, you are asking for the third occurrence of the letter a in the string abbabba, which is in the 7th position, when the position starts with 1 for the first position. 

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

SELECT dbo.CHARINDEX2('a', 'abbabba', 2) Returns 4

SELECT dbo.CHARINDEX2('a', 'abbabba', 1) Returns 1

SELECT dbo.CHARINDEX2('a', 'abbabba', 8) Returns 0

SELECT dbo.CHARINDEX2('q', 'abbabba', 1) Returns 0

SELECT dbo.CHARINDEX2('q', 'abbabba', 2) Returns 0

SELECT dbo.CHARINDEX2('a', 'abbabba', 0) Returns NULL

SELECT dbo.CHARINDEX2('q', 'abbabba', 0) Returns NULL

SELECT dbo.CHARINDEX2('q', 'abbabba', 7) Returns 0

Microsoft Excel

If you want to do the same thing in Microsoft Excel, have a look at our post called Excel Find Nth Occurrence of Char in a String. This post uses the Excel FIND and SUBSTITUTE functions.

Python

Are you also working with Python? In Python, we can Python Find the nth Occurrence of a Char in a String.

Leave a Reply