Is there a LastIndexOf in SQL Server?

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?

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()) + '_'))

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

No, SQL server doesnt have LastIndexOf.

This are the available string functions

But you can always can create your own function

CREATE FUNCTION dbo.LastIndexOf(@source text, @pattern char)  
RETURNS 
AS       
BEGIN  
    DECLARE @ret text;  
    SELECT into @ret
           REVERSE(SUBSTRING(REVERSE(@source), 1, 
           CHARINDEX(@pattern, REVERSE(@source), 1) - 1))
    RETURN @ret;  
END;  
GO

Is there a LastIndexOf in SQL Server?

Related

Recent Posts