CODEWITHSUNDEEP
sqlsql-server
Seth Johnson
Seth Johnson

Reputation: 23

Parse a string between 2 delimiters

I added the following line but I am getting an error "Invalid length parameter passed to the LEFT or SUBSTRING function":

substring(PointName, CHARINDEX(':',PointName)+1, CHARINDEX('-',PointName)-CHARINDEX(':',PointName)-1)

It will work if I remove the ">", so i am at a lost of my next step

SELECT * 
from RawAnalog RA
CROSS APPLY (
            VALUES(
                reverse(PARSENAME(REPLACE(REVERSE(pointname),'.','.'),2)), 
                reverse(PARSENAME(REPLACE(REVERSE(pointname),'.','.'),3)),
                substring(PointName, CHARINDEX(':',PointName)+1, CHARINDEX('-',PointName)-CHARINDEX(':',PointName)-1)
                )
            )
                V(TYPE,POINT,SITE )
Where 
          V.Point IN ('MaTmp', 'OaTmp', 'SaTmp', 'ChwVlvCmd','SaStP', 'SaCFM')
          and ra.UTCDateTime = '2021-01-01 00:15:00.000'
ORDER by V.POINT asc

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

Upvotes: 0

Views: 217

Answers (4)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22177

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 |
+----+-----------------------------------+----------------------------------------------+---------+

Upvotes: 0

yoma
yoma

Reputation: 369

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.

Upvotes: 1

FlexYourData
FlexYourData

Reputation: 2344

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);

Upvotes: 0

Thom A
Thom A

Reputation: 95564

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);

Upvotes: 2

Related Questions