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'
/hello/world/today/is/Friday/
Thanks!
Hi
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()
OVER(
ORDER BY (SELECT NULL)) AS rn,
value
FROM String_split(@STRING, '/'))
SELECT *
FROM cte
WHERE rn = (SELECT Max(rn)
FROM cte)
go
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
http://www.sqlservercentral.com/articles/Tally+Table/72993/
use sqlteam
go
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
)
SELECT *
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 ..
Hi
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
(
SELECT
ROW_NUMBER() over(order by (select null)) as rn
, C.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as X(C)
)
select
*
from
cte
where
rn = ( select max(rn) -1 from cte)
go
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
Jeff
first
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. 
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?