Nov 4, 2015

Get last index of character in a string SQL server

Below SQL Function returns position of last occurrence or last index in a string of specified sub string

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('A','AoneATwoAThree')


Output: 9

No comments: