Parse a string between 2 delimiters-CodePudding

I have the following string:

XRDCWPDBSADX02:SKYLINE-NAE04/FC-1.041020FE.ChwVlvCmd.#85

I need to pull out the SKYLINE value currently between two delimiters (: and -). I tried to use PARSENAME but I am getting too much text:

XRDCWPDBSADX02:SKYLINE-NAE04/FC-1.

How can I get the name "SKYLINE"?

SELECT * 
from RawAnalog RA
     CROSS APPLY 
      (VALUES(reverse(PARSENAME(REPLACE(REVERSE(pointname),'.','.'),2)),PARSENAME(pointname,4), 
       reverse(PARSENAME(REPLACE(REVERSE(pointname),'.','.'),3))))V(TYPE,SITE,POINT )

Desired output would be except for site I would just want "Skyline": output

CodePudding user response：

Use STUFF and CHARINDEX to remove the leading characters, and the LEFT and CHARINDEX to remove the trailing characters:

SELECT LEFT(S.YourString,ISNULL(NULLIF(CHARINDEX('-',S.YourString),0)-1,LEN(S.YourString))) AS NewString
FROM (VALUES('XRDCWPDBSADX02:SKYLINE-NAE04/FC-1.041020FE.ChwVlvCmd.#85'))V(YourString)
     CROSS APPLY (VALUES(STUFF(V.YourString,1,CHARINDEX(':',V.YourString),'')))S(YourString);

CodePudding user response：

Assuming you have a pattern where colon (':') appears only once right before word you want to extract. And right after the word you have a dash ('-'), but you also can have dashes before it:

declare @str nvarchar(max) = 'XRDCWPDBSADX02:SKYLINE-NAE04/FC-1.041020FE.ChwVlvCmd.#85'
select substring( @str, charindex(':', @str, 0)   1, charindex('-', @str, charindex(':', @str, 0)) - charindex(':', @str, 0) - 1)

The third argument in this part : charindex('-', @str, charindex(':', @str, 0)) is a starting position to search, which is an index of colon, in case we have some additional dashes before it.

CodePudding user response：

Another variation:

SELECT SUBSTRING(t,PATINDEX('%:%',t) 1,PATINDEX('%-%',t) - PATINDEX('%:%',t) - 1)
FROM (VALUES ('XRDCWPDBSADX02:SKYLINE-NAE04/FC-1.041020FE.ChwVlvCmd.#85 ')) tbl(t);

CodePudding user response：

Please try the following solution.

It will work starting from SQL Server 2016 onwards.

We are converting the Site column into a JSON array. After that it is very easy to pickup a needed token based on its position.

SQL

-- DDL and sample data population, start
DECLARE @RawAnalog TABLE (ID INT IDENTITY PRIMARY KEY, Site VARCHAR(100));
INSERT INTO @RawAnalog (Site) VALUES
('XRDCWPDBSADX02:SKYLINE-NAE04/FC-1'),
('XRDCWPDBSADX02:SKYLINE-NAE04/FC-2');
-- DDL and sample data population, end

SELECT *
    , Token  = JSON_VALUE(s, '$[1]')
FROM @RawAnalog
    CROSS APPLY (VALUES ('["' REPLACE(STRING_ESCAPE(REPLACE(Site,':','-'),'json'),'-','","') '"]')) As j(s);

Output

 ---- ----------------------------------- ---------------------------------------------- --------- 
| ID |               Site                |                      s                       |  Token  |
 ---- ----------------------------------- ---------------------------------------------- --------- 
|  1 | XRDCWPDBSADX02:SKYLINE-NAE04/FC-1 | ["XRDCWPDBSADX02","SKYLINE","NAE04\/FC","1"] | SKYLINE |
|  2 | XRDCWPDBSADX02:SKYLINE-NAE04/FC-2 | ["XRDCWPDBSADX02","SKYLINE","NAE04\/FC","2"] | SKYLINE |
 ---- ----------------------------------- ---------------------------------------------- ---------