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?

Ranket

can you show some examples of your string? – Orgulous

To me also, it is not only unreadable and not able to guess what you are trying to achieve without sample data :) – Bohlin

Gotta love Stack Overflow where people's first response is to ask for more info even though the question needs no more information to solve it :) – Retarder

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

Bagehot

I had to change the first expression to: right(db_name(), charindex('_', reverse(db_name()) + '_') - 1) to make this work! Otherwise great! – Vogt

The left should also have an offset analogous to right's "-1". It should be: select left(db_name(), len(db_name()) - charindex('_', reverse(db_name()) + '_') + 1) (I tried to edit, but the change had to be at least 6 characters.) – Byerly

Is the ` + '_'` part to handle the case where _ is not in the database name? – Dall

@Dall . . . Yes, that is the purpose. – Bagehot

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

Loadstone

Answer 3

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 

Latinism

Answer 4

CREATE FUNCTION dbo.LastIndexOf(@text NTEXT, @delimiter NTEXT)  
RETURNS INT
AS       
BEGIN  
  IF (@text IS NULL) RETURN NULL;
  IF (@delimiter IS NULL) RETURN NULL;
  DECLARE @Text2 AS NVARCHAR(MAX) = @text;
  DECLARE @Delimiter2 AS NVARCHAR(MAX) = @delimiter;
  DECLARE @Index AS INT = CHARINDEX(REVERSE(@Delimiter2), REVERSE(@Text2));
  IF (@Index < 1) RETURN 0;
  DECLARE @ContentLength AS INT = (LEN('|' + @Text2 + '|') - 2);
  DECLARE @DelimiterLength AS INT = (LEN('|' + @Delimiter2 + '|') - 2);
  DECLARE @Result AS INT = (@ContentLength - @Index - @DelimiterLength + 2);
  RETURN @Result;
END

• Allows for multi-character delimiters like ", " (comma space).
• Returns 0 if the delimiter is not found.
• Takes a NTEXT for comfort reasons as NVARCHAR(MAX)s are implicitely cast into NTEXT but not vice-versa.
• Handles delimiters with leading or tailing space correctly!

Vigilante

Answer 5

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

Coloration

Answer 6

Try:

select LEN('tran van abc') + 1 - CHARINDEX(' ', REVERSE('tran van abc'))

So, the last index of ' ' is : 9

Shopkeeper

What if the tested string - here: 'tran van abc' - does not contain the searched character? ;-) It will return last index = Len (..) + 1 ! – Monitorial

Answer 7

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

Clairvoyance

Answer 8

Try this.

drop table #temp
declare @brokername1 nvarchar(max)='indiabullssecurities,canmoney,indianivesh,acumencapitalmarket,sharekhan,edelweisscapital';
Create Table #temp
(
ID int identity(1,1) not null,
value varchar(100) not null
)

INSERT INTO #temp(value) SELECT value from STRING_SPLIT(@brokername1,',')
declare @id int;
set @id=(select max(id) from #temp)
--print @id
declare @results varchar(500)
select @results = coalesce(@results + ',', '') +  convert(varchar(12),value)
from #temp where id<@id
order by id
print @results

Flickertail