Tuesday, November 20

Nth Charindex / Nth Occurrence in SQL



Nth Charindex
In SQL server we have the char index which gives the position of the character of the first occurance. Now for example, If in a statement you want to find position of  a alphabetic character 'S' which is repeated many a times maybe 5 times or n times. To find its position at 5th occurance or at the nth occurance this function can be created or used.



ALTER function [dbo].[NThcharindex]
(
@char varchar(255),@string varchar(255),@start int,@occurence int
)
RETURNS int
AS
BEGIN
declare @i int
declare @j int
set @i=0
set @j=@start
while @i<@occurence
begin
set @j = (charindex(@char,@string,@j)+1)
set @i=@i+1
end
set @j=@j-1
 return @j
 end


Click here to know more about SQL- String Functions