CODEWITHSUNDEEP
sqlsql-server
Rob
Rob

Reputation: 61

T-SQL substring between two slashes to extract data

I am trying extract part of a string in T-SQL for a project I am working on.

Examples:

/Clients/AAA/Something/Something

/Clients/BBBB/Something/Something

I am specifically trying to extract the AAA or the BBB which are not a consistent number of characters.

Upvotes: 2

Views: 2664

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270001

I find that apply is convenient for expressing this type of logic:

with t as (
      select *
      from (values   ('/Clients/AAA/Something/Something/'), ('/Clients/bbbbb/Something/Something/')) t(str)
     )
select *, left(str2, charindex('/', str2) - 1)
from t cross apply
     (values (stuff(str, 1, patindex('%_/%', str) + 1, ''))) v(str2);

Note that this looks for the pattern _/ to find the second slash in the string.

Upvotes: 2

Mathew Paxinos
Mathew Paxinos

Reputation: 964

Try the following using CHARINDEX and SUBSTRING.

drop table #a
create table #a (d varchar(100))

insert into #a (d)
    values   ('/Clients/AAA/Something/Something/')
            ,('/Clients/bbbbb/Something/Something/')

select  d       as [OriginalData]
        ,charindex('/', d, charindex('/', d, 0)+1) as [SecondSlash]
        ,charindex('/', d, charindex('/', d, charindex('/', d, 0)+1)+1)  as [ThirdSlash]
        ,SUBSTRING(d    -- Value
                    , charindex('/', d, charindex('/', d, 0)+1)+1  -- Startpoint (SecondSlash) + 1
                    , charindex('/', d, charindex('/', d, charindex('/', d, 0)+1)+1) - charindex('/', d, charindex('/', d, 0)+1)-1) as [Extract]
                                        -- Endpoint (ThirdSlash - SecondSlash - 1)
from #a

It's a bit messy and will only return the text between the second and third slash, but it should be fairly quick.

Upvotes: 3

Dale K
Dale K

Reputation: 27250

select Data
  -- Find second slash
  , charindex('/', Data, 2)
  -- Find third slash
  , charindex('/', Data, charindex('/', Data, 2)+1)
  -- Find string between the second and third slash
  , substring(data, charindex('/', Data, 2) + 1, charindex('/', Data, charindex('/', Data, 2)+1) - charindex('/', Data, 2) - 1)
from (
  select '/Clients/AAA/Something/Something' Data
  union all select '/Clients/BBBB/Something/Something'
) x

Upvotes: 0

Related Questions