Extracting Data Between Two Delimiters in SQL Server

Extracting data between two delimiters in SQL Server?

If you have a string that are exactly same format (three times) delimited by | then you can PARSENAME() :

select col1, parsename(cols, 3) fisrt, parsename(cols, 2) second, parsename(cols, 1) third
from table1 t1 cross apply
     ( values (replace(col1, '|', '.')) 
     ) t2 (cols);

How to extract a string between two of the SAME delimiters T-SQL?

Use CHAR_INDEX twice:

SELECT *, SUBSTRING(path, pos1 + 1, pos2 - pos1 - 1)
FROM tests
CROSS APPLY (SELECT NULLIF(CHARINDEX('\', path), 0)) AS ca1(pos1)
CROSS APPLY (SELECT NULLIF(CHARINDEX('\', path, pos1 + 1), 0)) AS ca2(pos2)

-- NULLIF is used to convert 0 value (character not found) to NULL

Test on db<>fiddle

Extract substring between two delimiters and output into calculated column

One method is a few nested calls to CHARINDEX. I do this in some VALUES constructs, so as not to need to repeat the calls:

SELECT SUBSTRING(V.YourColumn,CI2.I+1, CI3.I - CI2.I - 1) AS YourSubString
FROM (VALUES('ABCD-0291 job SRT_FBEW_DNBADQAAGR in process published with status code 32'),
            ('ABCD-0431 job DBDE_FKFW_MDNSQA in process published with status code 14'),
            ('ABCD-0075 job SHFGF_RTBL_MJBVXW in process published with status code 19'))V(YourColumn)
      CROSS APPLY (VALUES(CHARINDEX(' ',V.YourColumn)))CI1(I)
      CROSS APPLY (VALUES(CHARINDEX(' ',V.YourColumn,CI1.I + 1)))CI2(I)
      CROSS APPLY (VALUES(CHARINDEX(' ',V.YourColumn,CI2.I + 1)))CI3(I)

db<>fiddle

This will, however, fail if there are not 3 white spaces in your value.

---

Seems like the OP actually wants this as a computed column. Rather than using a different solution, I have taken the above, and turned it into a scalar expression:

ALTER TABLE dbo.YourTable ADD YourSubString AS SUBSTRING(YourColumn,CHARINDEX(' ',YourColumn,CHARINDEX(' ',YourColumn) + 1) + 1, CHARINDEX(' ',YourColumn,CHARINDEX(' ',YourColumn,CHARINDEX(' ',YourColumn) + 1) + 1) - CHARINDEX(' ',YourColumn,CHARINDEX(' ',YourColumn) + 1) - 1);

db<>fiddle

Again, if there aren't 3 whitespaces this will generate an error; I am assuming that all the values are valid for the above expression in your source data. If this is not the case, I would suggest asking a new question now, as the goal posts have already been moved once.

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

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.

String Between Two delimiters ('_')

This should work:

SELECT Field, SUBSTRING(Field,0,CHARINDEX('_',Field,0)) AS POS1, 
SUBSTRING(SUBSTRING(Field, CHARINDEX('_',Field)+1, LEN(Field)), 0, CHARINDEX('_',SUBSTRING(Field, CHARINDEX('_',Field)+1, LEN(Field)),0)) AS POS2 ,
SUBSTRING(Field, 3 + LEN(SUBSTRING(Field,0,CHARINDEX('_',Field,0))) + LEN(SUBSTRING(SUBSTRING(Field, CHARINDEX('_',Field)+1, LEN(Field)), 0, CHARINDEX('_',SUBSTRING(Field, CHARINDEX('_',Field)+1, LEN(Field)),0))), LEN(Field))  AS POS3
FROM YOUR_TABLE

Retrieve String Between Two Delimiters for Multiple Occurences SQL Server

See inline comments for an overview of what's going on:

DECLARE @MyString varchar(256) = '$I10~TESTING1$XYZ$I10~TESTING2$~'
, @pre char(5) = '$I10~' --this appears before the string we want
, @post char(1) = '$' --this appears after it
select 
--take part of
substring( 
    --the input string
    @MyString     
    --starting from the first pre-delimiter (but add on the length of the delimeter so we exclude the delimeter itself)
    ,charindex(@pre,@MyString) + len(@pre) 
    --and ending at the first post-delimiter to appear after the first pre-delimeter
    , charindex(@post,@MyString,charindex(@pre,@MyString) + len(@pre)) - (charindex(@pre,@MyString) + len(@pre))
) 
,
--for the second result do the same as above
substring(
    @MyString
    --only now we're looking for the second pre-delimiter (aka the first after the first)
    ,charindex(@pre,@MyString,charindex(@pre,@MyString) + len(@pre)) + len(@pre)
    --and the second post-delimiter
    ,charindex(@post,@MyString,charindex(@pre,@MyString,charindex(@pre,@MyString) + len(@pre)) + len(@pre)) - (charindex(@pre,@MyString,charindex(@pre,@MyString) + len(@pre)) + len(@pre))
) 

NB: this assumes that the pre delimiter doesn't appear between the pre and post delimiter; if it did that could get confusing / we'd need to determine the desired behaviour.

substring(@stringToBreakApart, @indexOfFirstCharacterInSubstring, @lengthOfResultingString) - returns a segment of the original string.

charindex(@stringToFind, @stringToSearch, @indexOfFirstCharacterToLookAt) - returns the index of the first character of a given substring within a given string.

len(@stringToAnalyze) - returns the number of characters (length) of the given string.

Update

Per comments, here's how you could return a single column which splits the string on the delimiters (ignoring anything not between pre and post) and then joins the results to form a comma delimited field.

DECLARE @MyString varchar(256) = '$I10~TESTING1$XYZ$I10~TESTING2$~$I10~TESTING3$...'
, @pre char(5) = '$I10~' --this appears before the string we want
, @post char(1) = '$' --this appears after it
, @newDelim char(1) = ','
;with cte(indx, firstCharIndex, lastCharIndex) as 
(
    select 0 
    , charindex(@pre,@MyString) + len(@pre) 
    , charindex(@post,@MyString,charindex(@pre,@MyString) + len(@pre)) 

    union all

    select indx + 1
    , charindex(@pre,@MyString, lastCharIndex + len(@post)) + len(@pre) 
    , charindex(@post,@MyString,charindex(@pre,@MyString, lastCharIndex + len(@post)) + len(@pre)) 
    from cte
    where charindex(@pre,@MyString, lastCharIndex + len(@post)) > 0
)
, cte2 (substr, indx ) as 
(
    select cast(substring(@MyString, firstCharIndex, lastCharIndex - firstCharIndex) as nvarchar(max)) 
    , indx
    from cte 
    where indx = (select max(indx) from cte)

    union all

    select substring(@MyString, firstCharIndex, lastCharIndex - firstCharIndex) + @newDelim + cte2.substr
    , cte.indx
    from cte
    inner join cte2 on cte2.indx = cte.indx + 1 

)
select * from cte2 where indx = 0

Extract parts of string separated by delimiter

SQL Server 2016+

Concept using STRING_SPLIT(), PARSENAME(), PIVOT

-- Mimic Table named z_tbl_tmp
DECLARE @z_tbl_tmp TABLE (id INT, OPTIONAL_FIELD_1 NVARCHAR(max));
INSERT INTO @z_tbl_tmp VALUES (1, N'Growth: Compliance;Priority: Contractual;Original Vendor: ABC SERVICES;');
INSERT INTO @z_tbl_tmp VALUES (2, N'Growth: Run; Priority: Critical - Turns Contractual');
-- 

-- Pivot Parsed Data
WITH tbl_parsed AS (
    -- Parse Data into Key Value Pairs
    SELECT id, 
        TRIM(PARSENAME(REPLACE(value,': ','.'), 2)) AS K, 
        TRIM(PARSENAME(REPLACE(value,': ','.'), 1)) AS V 
    FROM @z_tbl_tmp
        CROSS APPLY STRING_SPLIT(OPTIONAL_FIELD_1,';')
)
SELECT id, [Growth] AS GROWTH_TXT, [Priority] AS PRIORITY_TXT
FROM tbl_parsed
    PIVOT (MAX(V) FOR [K] IN ([Growth], [Priority])) AS pvt

+----+------------+-------------------------------+
| id | GROWTH_TXT | PRIORITY_TXT                  |
+----+------------+-------------------------------+
|  1 | Compliance | Contractual                   |
+----+------------+-------------------------------+
|  2 | Run        | Critical - Turns Contractual  |
+----+------------+-------------------------------+

Retrieve text between two periods in a value

We can build on your current attempt:

substring(
    result,
    charindex('.', result) + 1,
    charindex('.', result, charindex('.', result) + 1) - charindex('.', result) - 1
)

Rationale: you alreay have the first two arguments to substring() right. The third argument defines the number of characters to capture. For this, we compute the position of the next dot (.) with expression: charindex('.', result, charindex('.', result) + 1). Then we substract the position of the first dot from that value, which gives us the number of characters that we should capture.

Demo on DB Fiddle:

result                   | result_mid
:----------------------- | :---------
sam.pdc.sys.paas.l.com   | pdc       
sm.ridl.sys.paas.m.com   | ridl      
s.sandbox.sys.paas.g.com | sandbox   

---

Related Topics