SQLTeam.com | Weblogs | Forums

Strip out string - get all characters between last 2 slashes


#1

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!


#2

Hi

Hope this helps. ...just clues.
:grinning:
:grinning:
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 '.'


#3

Or you can use

Split_String() function


#4


#5

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


#6

does string_split work for you ??

i can do the SQL also if you need
:slight_smile:
:slight_smile:


#7

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


#8

here is one way

please let me know if it helps you
:slight_smile:
:slight_smile:

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

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

image


#9

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

#10

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

#11

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

image


#12

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


#13

Oh ok

Thanks Scott

Was not aware of it ..

:grinning::grinning: