Tags: Database , Stored Procedure

I was working around Stored Procedure in MSSQL mean while I get to know that there ain't any function called lastIndexOf(). So I coded it and thought let me share it with you guys. Its one time execution for each database and you can use it in queries, stored procedure, etc...

CREATE FUNCTION [dbo].[lastIndexOf] (@String VARCHAR(max) ,@Find VARCHAR(100))
RETURNS INT
AS
BEGIN
 DECLARE @SPLITTED_STRING VARCHAR(500);
 DECLARE @INDEX INT;
 DECLARE @PREVIOUS_INDEX INT;
 DECLARE @LEN INT;
 
 SET @INDEX = 0;
 SET @PREVIOUS_INDEX = 0;
 SET @SPLITTED_STRING = @String;
 
 WHILE ((SELECT CHARINDEX(@Find, @SPLITTED_STRING)) > 0)
  BEGIN
   SELECT @PREVIOUS_INDEX = CHARINDEX(@Find, @SPLITTED_STRING);
   SELECT @LEN = LEN(@SPLITTED_STRING);
   SELECT @SPLITTED_STRING = SUBSTRING(@SPLITTED_STRING, @PREVIOUS_INDEX+1, @LEN);
   SET @INDEX = @PREVIOUS_INDEX + @INDEX;
  END
  
 RETURN @INDEX - 1;
END

How to run this script in MSSQL?
Just copy the above script and paste it in SQL query editor. Press F5 or execute it.

How to use lastIndexOf function?
To get index of specified text execute your query like...

DECLARE @location int;
SET @location = [dbo].[lastIndexOf] ('ABCXYZ','C');
PRINT @location
//output : 2
DECLARE @location int;
SET @location = [dbo].[lastIndexOf] ('ABCXYZ','A');
PRINT @location
//output : 0
DECLARE @location int;
SET @location = [dbo].[lastIndexOf] ('ABCXYZ','F');
PRINT @location
//output : -1
DECLARE @location int;
SET @location = [dbo].[lastIndexOf] ('ABCXYZ','AT');
PRINT @location
//output : -1

Tags: Database , Stored Procedure