Nov 6, 2015

Find index position of the last occurrence of a specified string within another string SQL Server

SQL function which get last index position of a characters in a string in SQL server

CREATE FUNCTION [dbo].[LastIndexOf] (
      @Chars VARCHAR(10),
      @String VARCHAR(MAX))
RETURNS INT
AS
BEGIN
      IF CHARINDEX(@Chars, @String) = 0
            RETURN 0
      ELSE
            RETURN 2 + DATALENGTH(@String) - CHARINDEX(REVERSE(@Chars), REVERSE(@String)) - DATALENGTH(@Chars)

      RETURN 0
END

Example:

SELECT [dbo].[LastIndexOf]('ab', 'SQLabMySQLabOracleabSQLSERVER')


Output: 19

No comments:

Post a Comment