SQLTeam.com | Weblogs | Forums

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)), '/', '')


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 ..