Extract data between two characters(=,/, ,etc)-CodePudding

I have a column name(fle) and the data looks like /vr/gg/ercs/nyt_dte/segment/xml/subnetwor/=hhh/bvth=lhyt012 I need to extract the data which is between = and / example hhh.

CodePudding user response：

You could achieve that by finding indexes of both characters and using substring function. Here's example query:

declare @sampleString nvarchar(500) = '/vr/gg/ercs/nyt_dte/segment/xml/subnetwor/=hhh/bvth=lhyt012';

select
    substring(@sampleString, OpeningCharIndex   1, ClosingCharIndex - OpeningCharIndex - 1)
from (
    select 
        OpeningCharIndex,
        CHARINDEX('/', @sampleString, OpeningCharIndex) ClosingCharIndex
    from (
        select
            CHARINDEX('=', @sampleString) OpeningCharIndex
    ) a
) a

CodePudding user response：

Using the data you gave us:

create table MyTable (
  MyColumn varchar(100)
)

insert into MyTable values ('/vr/gg/ercs/nyt_dte/segment/xml/subnetwor/=hhh/bvth=lhyt012')

This simple select is what you want:

SELECT SUBSTRING(
    MyColumn, 
    charindex('=', MyColumn)   1,
    CHARINDEX('/', substring(MyColumn, charindex('=', MyColumn)   2, LEN(MyColumn)))
)
from MyTable

SQL Fiddle: http://sqlfiddle.com/#!18/374677/3