Strip out string - get all characters between last 2 slashes

From the example string below I need to get the characters between the last 2 slashes in SQL Server 2016. Strings always end in a slash. Can anyone help?

Need 'Friday'




Hope this helps. ...just clues.
This is one way.
Several ways there

SELECT substring_index(your_column, '.', -1) FROM test_table

this will match the string from the right to the first occurance of '.' (from the right)

SELECT substring(your_column from length(substring_index(your_column, '.', 2))+2) FROM test_table

this will match string after second occurance of '.'

Or you can use

Split_String() function

thanks, however substring_index doesn't work in SQL Server.

does string_split work for you ??

i can do the SQL also if you need

Haven't tried that - much appreciated if you could, thankyou

here is one way

please let me know if it helps you

DECLARE @STRING VARCHAR(100) = '/hello/world/today/is/Friday/'

select replace(right(@STRING,charindex('/',@STRING,charindex('/',@STRING)+1)),'/','')


hi another way
using String_split

does this work for you ALSO ??

string and SQL ...
DECLARE @STRING VARCHAR(100) = '/hello/world/today/is/Friday/'; 

WITH cte 
     AS (SELECT Row_number() 
                    ORDER BY (SELECT NULL)) AS rn, 
         FROM   String_split(@STRING, '/')) 
FROM   cte 
WHERE  rn = (SELECT Max(rn) 
             FROM   cte) 


You cannot safely use STRING_SPLIT for this, because it does not guarantee the order of the returned strings; that is, the first string could end up being the last one returned.

Here's one way to do it:

DECLARE @string varchar(100) = '/hello/world/today/is/Friday/'
SELECT REPLACE(RIGHT(@string, CHARINDEX('/', REVERSE(@string), 2)), '/', '')
1 Like

or another way per @JeffModen

use sqlteam

create table #booya(trans varchar(250))

insert into #booya
select '/hello/world/today/is/Friday/iooi/Monday/a98787/Saturday'
;with cte as
select  1 as rn, 
        CAST('name1' AS nVARCHAR(255)) as weekdayname
union all
select  rn+1,
        weekdayname = cast(DATENAME(weekday, GETDATE()-rn) AS nVARCHAR(255))
from cte a where rn <= 7
   FROM #booya t
  CROSS APPLY dbo.[DelimitedSplit8K](t.trans,N'/') split
join cte on cte.weekdayname = split.Item

drop table #booya


select replace(reverse(substring(reverse(@string),1,charindex('/',reverse(@string),2)-1)),'/','')

Oh ok

Thanks Scott

Was not aware of it ..



this is a XML approach .... may not have any value to this approach .. something different

please click arrow to the left for XML approach
DECLARE @xml xml, @str varchar(100), @delimiter varchar(10)

SET @str = '/hello/world/today/is/Friday/'
SET @delimiter = '/'
SET @xml = cast(('<X>'+replace(@str, @delimiter, '</X><X>')+'</X>') as xml)

; with cte as 
       ROW_NUMBER() over(order by (select null)) as rn 
     , C.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as X(C)
	   rn = ( select max(rn) -1 from cte) 


THE XML approach is actually one of the slowest and most resource intensive methods there is.

yes there is this function called DelimitedSplit8K written by this kind of nice guy.

thanks for pointing it out

we make list of
"different approaches" ... their pro's and con's

and pick the one that suits our needs Best


You don't understand, Harish... What you're saying is correct but you're a part of a different "we". Your "we" is just learning the different approaches and apparently have not learned the pro's and con's you speak of. You made a "recommendation" by posting an answer and that's good but when I suggested that the XML method is one of the slowest and most resource intensive, you missed the perfect opportunity to say "I didn't know that... can you show me more?" so that you can learn of the very pro's and con's you're talking about.

My aim wasn't to slam your answer. My aim was to make you ask for more information. If you want to learn the pro's and con's, you have to ask for more information when someone challenges one of your answers.

And there is no XML splitter method that "suits our needs Best" for this type of string splitting (concatenation required to make it look like XML). Not even for small strings. It's a terribly slow and resource intensive method.

Ok... your turn... ask the question that needs to be asked. :wink:

Thank you for both your observation and your kindness. I'm humbled, good Sir.

Hi, Frank,

I'm interested in the problem you posted and have a couple of questions about the definition of the problem.

Will you always want what's between the last two slashes even if it's not the name of a week day?
If so, will there always be at least two slashes in the string?
If so, Scott's answer will probably be the fastest and least resource intensive with one caveat. The performance of REPLACE is highly dependent on the current collation of the column. What collation are you using?

If you're actually looking for the names of Weekdays ANYWHERE in the string, @yosiasz 's method looks fine but it contains an rCTE (Recursive CTE) that produces and incremental count, which is also pretty nasty when it comes to performance and resource usage even for such small things as well as it having a data type mismatch, which can also be pretty expensive.

So, which is it? Are you looking for whatever is between the last two slashes regardless of content or something a little more complex?