Is there a LastIndexOf in SQL Server?

Question

I am trying to parse out a value from a string that involves getting the last index of a string. Currently, I am doing a horrible hack that involves reversing a string:

SELECT REVERSE(SUBSTRING(REVERSE(DB_NAME()), 1,      CHARINDEX('_', REVERSE(DB_NAME()), 1) - 1)) 

To me this code is nearly unreadable. I just upgraded to SQL Server 2016 and I hoping there is a better way. Is there?

Answer 1

If you want everything after the last _, then use:

select right(db_name(), charindex('_', reverse(db_name()) + '_') - 1) 

If you want everything before, then use left():

select left(db_name(), len(db_name()) - charindex('_', reverse(db_name()) + '_')) 

Answer 2

Wrote 2 functions, 1 to return LastIndexOf for the selected character.

CREATE FUNCTION dbo.LastIndexOf(@source nvarchar(80), @pattern char) RETURNS int BEGIN          RETURN (LEN(@source)) -  CHARINDEX(@pattern, REVERSE(@source))  END;   GO 

and 1 to return a string before this LastIndexOf. Maybe it will be useful to someone.

CREATE FUNCTION dbo.StringBeforeLastIndex(@source nvarchar(80), @pattern char) RETURNS nvarchar(80) BEGIN          DECLARE @lastIndex int        SET @lastIndex = (LEN(@source)) -  CHARINDEX(@pattern, REVERSE(@source))        RETURN SUBSTRING(@source, 0, @lastindex + 1)       -- +1 because index starts at 0, but length at 1, so to get up to 11th index, we need LENGTH 11+1=12 END;   GO