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

Question

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

if you can put more info, such as source string is xxxxx, and the expected string is yyy, that would help people to understand you better. — jyao, Oct 22 '19 at 15:28
You should strive to work with normalized data in SQL. Using delimited strings to store multiple different data points in the same column, argument or variable is really a bad practice for many reasons - read about them [here](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad). — Zohar Peled, Oct 22 '19 at 15:34
@ZoharPeled There are use cases where storing a path in a database table is not necessarily bad practice. For example, it could be coming from the application layer (but, in that case, it might make more sense to handle this requirement _in_ the application layer). — Tim Biegeleisen, Oct 22 '19 at 15:38
@TimBiegeleisen A path is not a delimited string. Admittedly, it might be my fault to not recognize the string in the question as a path - however a path is one (composite) data point. That wasn't the case I was talking about. I was talking about stuff like storing `1,2,3,4` or `a/b/c/d` where each part of the delimited string is an "item". — Zohar Peled, Oct 22 '19 at 15:41

Tim Biegeleisen · Accepted Answer · 2019-10-22T15:43:02.093

1

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.

edited Oct 22 '19 at 15:43

answered Oct 22 '19 at 15:31

Tim Biegeleisen

502,043
27
286
360

It is throwing error as 'Invalid length parameter passed to the LEFT or SUBSTRING function'. – Aditi Singh Oct 22 '19 at 15:41
1

@AditiSingh this can only mean that some of your strings contain less than two delimiters. – Zohar Peled Oct 22 '19 at 15:42
1

@AditiSingh Zohar is correct, and most likely you have at least one path in there with _less_ than two `/`. I updated my answer to return `NA` for those paths with less than two delimiters. – Tim Biegeleisen Oct 22 '19 at 15:43
@TimBiegeleisen yes some of the strings have only one delimiters. – Aditi Singh Oct 22 '19 at 16:00

Radagast · Answer 2 · 2019-10-22T16:47:52.373

1

An ugly, but potentially more flexible approach building off of Tim's answer where you can specify which part of the string you want. I updated this solution to work with strings that may not start with a delimiter.

with cte1 as (select '1064_MyHoldings/' as Path)

,cte2 as (select case when left(Path,1)<>'/' then  '/'+ Path else Path end as Path from cte1)

select  case when Path like '%/%/%'
         then cast('<x>'+replace(Path,'/','</x><x>')+'</x>' as xml).value(N'/x[2]','varchar(50)')
         else right(Path,len(Path)-1) end as first_component
from cte2

The x[2] parameter is what you would change to get different parts of the string. It started with 2 because you have a delimiter before the string. Following the logic, x[3] would get you 'ONLINE' for you example string. The ELSE is not set to 'NA' because I thought you would want the first element of your string when you have less than 2 delimiters.

edited Oct 22 '19 at 16:47

answered Oct 22 '19 at 16:14

Radagast

5,102
3
12
27

@MeckelsDiverticulum really a flexible approach – Aditi Singh Oct 22 '19 at 16:27
1

@AditiSingh I modified it a little bit so it also works with strings that don't start with a delimiter. – Radagast Oct 22 '19 at 16:50
1

@MeckelsDiverticulum it was a wow approach thanks alot it already solved my all upcoming scenarios....my future is sorted thanks Meck (: – Aditi Singh Oct 23 '19 at 07:42

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

2 Answers2

Demo