How to find Last index of character in SQL Server 2008
Many times we need last index of specific character in given string (file processing/name processing)
In SQL Server.
Below is the function for it. you just need to run below function in your sql server.
And need to pass to parameter .
1) @strChar= String from which you find the last index of some Character.
2) @strChar = string/Character
CREATE FUNCTION LastIndexOf
(@strValue VARCHAR(4000),
@strChar VARCHAR(50))
RETURNS INT
AS
BEGIN
DECLARE @index INT
SET @index = 0
WHILE CHARINDEX(@strChar, @strValue) > 0
BEGIN
SET @index = @index + CASE WHEN CHARINDEX(@strChar, @strValue) > 1
THEN
(LEN(@strValue) - LEN(SUBSTRING(@strValue,CHARINDEX(@strChar, @strValue) + LEN(@strChar),LEN(@strValue))))
ELSE
1
END
SET @strValue = SUBSTRING(@strValue,CHARINDEX(@strChar, @strValue) + len(@strChar),LEN(@strValue))
END
RETURN @index
END
==============================================
RUN BELOW QUERY will return 6.
select DBO.LastIndexOf('12345.wmv','.')
===========================================
