Writing a LastIndexOf Function in SQL Server

Hello everyone,

In this article, I will try to give information about writing the LastIndexOf function in SQL Server.

First of all, there is no such function in SQL Server. Before writing the function, let’s get a brief overview of the CHARINDEX function.

In SQL Server, the CHARINDEX function performs a search operation starting from the beginning and returns the position of the first character it finds. The LastIndexOf function we will write performs a search starting from the end and returns the location of the character.

You can easily do this using the function below.

CREATE FUNCTION dbo.LastIndexOf
(
    @aramayapilacakmetin NTEXT,
    @aranacakkarakter NTEXT
)
RETURNS INT
AS
BEGIN
    IF (@aramayapilacakmetin IS NULL)
        RETURN NULL;
    IF (@aranacakkarakter IS NULL)
        RETURN NULL;
    DECLARE @aramayapilacakmetin2 AS NVARCHAR(MAX) = @aramayapilacakmetin;
    DECLARE @aranacakkarakter2 AS NVARCHAR(MAX) = @aranacakkarakter;
    DECLARE @index AS INT = CHARINDEX(REVERSE(@aranacakkarakter2), REVERSE(@aramayapilacakmetin2));
    IF (@index < 1)
        RETURN 0;
    DECLARE @icerikuzunluk AS INT = (LEN('|' + @aramayapilacakmetin2 + '|') - 2);
    DECLARE @aranacakkarakterLength AS INT = (LEN('|' + @aranacakkarakter2 + '|') - 2);
    DECLARE @sonuc AS INT = (@icerikuzunluk - @index - @aranacakkarakterLength + 2);
    RETURN @sonuc;
END;

--Use of Function

SELECT dbo.LastIndexOf('Yavuz Selim Kart','a')

When you create the above function and run the code, you will see a result similar to the one below.

As can be seen, it searches for the letter a starting from the end and returns the relevant result.

Good luck to everyone in business and life.