How to extract substrings from multiple delimiters like '\' in SQL Server 2014?

I have a string like this '/1064_MyHoldings/ONLINE/Adhoc/Rpt_CompanyCodeElig' i just want to extract '1064_MyHoldings' only like wise there are different strings like this where i just want 1st part of string between the first two delimiters?

select 
    left(Applications, Len(Path) - CHARINDEX('/', Applications) + 1) FolderName 
from 
    (select distinct 
         Substring(C.Path, CHARINDEX('/', C.Path) + 1, LEN(C.Path) - CHARINDEX('/', C.Path)) Applications) Result

where Applications is the column name having data like '/1064_MyHoldings/ONLINE/Adhoc/Rpt_CompanyCodeElig'

Expected result should Be '1064_MyHoldings' that is only the first part of string between the first two '/'

Solution

We can leverage the base string functions here. For example, to find the substring contained within the first two /, we can try:

WITH yourTable AS (
    SELECT '/1064_MyHoldings/ONLINE/Adhoc/Rpt_CompanyCodeElig' AS Path
)

SELECT
    CASE WHEN Path LIKE '%/%/%' THEN
    SUBSTRING(Path,
              CHARINDEX('/', Path) + 1,
              CHARINDEX('/', Path, CHARINDEX('/', Path) + 1) - CHARINDEX('/', Path) - 1)
         ELSE 'NA' END AS first_component
FROM yourTable;

Demo

What makes the above logic possible, for the first path component as well as potentially any component, is that CHARINDEX has an optional third parameter which lets us specify the starting position to search for a separator /.

Note: If you are certain that the first character of Path would always be just a /, then there is a much simpler query you could use:

SELECT
    CASE WHEN Path LIKE '%/%/%'
         THEN SUBSTRING(Path, 2, CHARINDEX('/', Path, 2) - 2)
         ELSE 'NA' END AS first_component
FROM yourTable;

But, this would only work again if you know that / be the first character in the path. Otherwise, you would need to use the more verbose version above.