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?

  • Avoid duplicate names in a dataset
  • Comparing with null by converting value to a constant with isnull
  • Issue creating a connection to SQL server 2014 with ODBC data source and TLS activated
  • Check if the nullable column is sparsed or not query in SQL Server
  • sql composite key with identity
  • How to compare different queries without autotrace
  • 4 Solutions collect form web for “Is there a LastIndexOf in SQL Server?”

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

    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 
    

    I came across this thread while searching for a solution to my similar problem which had the exact same requirement but was for a different kind of database that was lacking the REVERSE function.

    In my case this was for a OpenEdge (Progress) database, which has a slightly different syntax. This made the INSTR function available to me that most Oracle typed databases offer.

    So I came up with the following code:

    SELECT 
      INSTR(foo.filepath, '/',1, LENGTH(foo.filepath) - LENGTH( REPLACE( foo.filepath, '/',  ''))) AS IndexOfLastSlash 
    FROM foo
    

    However, for my specific situation (being the OpenEdge (Progress) database) this did not result into the desired behaviour because replacing the character with an empty char gave the same length as the original string. This doesn’t make much sense to me but I was able to bypass the problem with the code below:

    SELECT 
      INSTR(foo.filepath, '/',1, LENGTH( REPLACE( foo.filepath, '/',  'XX')) - LENGTH(foo.filepath))  AS IndexOfLastSlash 
    FROM foo
    

    Now I understand that this code won’t solve the problem for T-SQL because there is no alternative to the INSTR function that offers the Occurence property.

    Just to be thorough I’ll add the code needed to create this scalar function so it can be used the same way like I did in the above examples. And will do exactly what the OP wanted, serve as a LastIndexOf method for SQL Server.

      -- Drop the function if it already exists
      IF OBJECT_ID('INSTR', 'FN') IS NOT NULL
        DROP FUNCTION INSTR
      GO
    
      -- User-defined function to implement Oracle INSTR in SQL Server
      CREATE FUNCTION INSTR (@str VARCHAR(8000), @substr VARCHAR(255), @start INT, @occurrence INT)
      RETURNS INT
      AS
      BEGIN
        DECLARE @found INT = @occurrence,
                @pos INT = @start;
    
        WHILE 1=1 
        BEGIN
            -- Find the next occurrence
            SET @pos = CHARINDEX(@substr, @str, @pos);
    
            -- Nothing found
            IF @pos IS NULL OR @pos = 0
                RETURN @pos;
    
            -- The required occurrence found
            IF @found = 1
                BREAK;
    
            -- Prepare to find another one occurrence
            SET @found = @found - 1;
            SET @pos = @pos + 1;
        END
    
        RETURN @pos;
      END
      GO
    

    To avoid the obvious, when the REVERSE function is available you do not need to create this scalar function and you can just get the required result like this:

    SELECT
      LEN(foo.filepath) - CHARINDEX('\', REVERSE(foo.filepath))+1 AS LastIndexOfSlash 
    FROM foo
    

    Once you have one of the split strings from here,you can do it in a set based way like this..

    declare @string varchar(max)
    set @string='C:\Program Files\Microsoft SQL Server\MSSQL\DATA\AdventureWorks_Data.mdf'
    
    ;with cte
    as
    (select *,row_number() over (order by (select null)) as rownum
    from [dbo].[SplitStrings_Numbers](@string,'\')
    )
    select top 1 item from cte order by rownum desc
    
    **Output:**  
    AdventureWorks_Data.mdf
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.