In TSQL, how can I get the rindex functionality(search a substring from right and get the position)?

Question

Hi guys,

In TSQL, how can I get the rindex functionality(search a substring from right and get the position)?

Great thanks.

Answer 1

Here you go, fresh from my querypane:

CREATE FUNCTION fn_lastIndexOf(@search VARCHAR(max), @find VARCHAR(max)) RETURNS INT AS
BEGIN
    DECLARE @x INT, @y INT
    IF @search IS NULL OR @find IS NULL RETURN -1
    SET @y = LEN(@find)
    SET @x = LEN(@search) - @y + 1
    IF @x < @y RETURN -1

    WHILE @x > 0 BEGIN
        IF SUBSTRING(@search,@x,@y) = @find RETURN @x
        SET @x = @x - 1
    END

    RETURN -1
END
GO

-- usage
SELECT dbo.fn_lastIndexOf('Hello World','World') -- 7

SELECT SUBSTRING(name,dbo.fn_lastIndexOf(name,' '),9999) FROM emp

Answer 2

You can make use of REVERSE to do this:

e.g. to find the last position of "A"...

DECLARE @Val VARCHAR(100)
SET @Val = 'ABCDEFGAB'

SELECT LEN (@Val) - CHARINDEX('A', REVERSE(@Val)) + 1

Just put in a pre-check to see if the character does actually exist in the string first, and away you go.

Answer 3

this is way better than looping:

DECLARE @Value  varchar(50)
       ,@Search varchar(100)
               --1234567890
SELECT @Value = 'ABCDEFGAB'
      ,@Search=     'EFG'

SELECT LEN(@Value) - CHARINDEX(REVERSE(@Search), REVERSE(@Value)) +1 - (LEN(@Search)-1)

and works on any length search string.

try this function:

CREATE FUNCTION dbo.rindex
(    @Search varchar(5000)
    ,@Value  varchar(max)
)
RETURNS INT AS
BEGIN
    DECLARE @Location int
    SELECT @Location=CHARINDEX(REVERSE(@Search), REVERSE(@Value))

    RETURN CASE
               WHEN @Search IS NULL OR @Value IS NULL THEN -1
               WHEN @Location=0 THEN -1
               ELSE LEN(@Value) - @Location+1 - (LEN(@Search)-1)
           END
END
GO

Usage:

                         --123456789+123456789+
PRINT dbo.rindex('ABC',   'ABC zzzzz  ABC')      --returns 12
PRINT dbo.rindex('AAA',   'ABC zzzzz  ABC')      --returns -1
PRINT dbo.rindex('ABCD',  'ABCD zzzzz  ABC')     --returns 1
PRINT dbo.rindex('ABC',   'ABC zzABCzzz  ABCA')  --returns 15