Regex in SSMS 2016

Question

I am using ssms 2016. I have strings with following pattern 14Jan22_B_15May23_15_N_323_412_102 I need to extract from it two things.

1. First number between two _. In this case it is 15, but it can be any number. I tried below method but it didn't work. it returns 0

DECLARE @InputString VARCHAR(MAX) = '14Jan22_B_15May23_15_N_323_412_102'; 
DECLARE @MatchPos INT = PATINDEX('%_[0-9]+_%', @InputString);
select @MatchPos

2. All numbers after _N_ or _R_. Also this _N_ or _R_ needs to be after number from point 1. Because letter B can also be letter N.

I didn't start working on point 2, as I don't know how to resolve point 1

Answer 1

There are a few things wrong with your attempt that you may not catch right away. One of those issues is the '_' used in '%_[0-9]+_%' is a wildcard for a single character. The 2nd issue is that 15, is not a number between 0 and 9, in which you have a single character search for a number [0-9].

Maybe not the cleanest, but it works:

DECLARE @InputString VARCHAR(MAX) = '14Jan22_B_15May23_15_N_323_412_102'; 
DECLARE @MatchPos int = ISNULL(NULLIF(PATINDEX('%[_][0-9][_]%', @InputString),0),PATINDEX('%[_][0-9][0-9][_]%', @InputString))
DECLARE @num int = CHARINDEX('_N_',@InputString,@MatchPos)+3
DECLARE @t nvarchar(MAX) = SUBSTRING(@InputString,@num,LEN(@inputString)-@num)

;WITH CTE AS (
    SELECT 
        REPLACE(LEFT(@t,CHARINDEX('_',@t)),'_','') [NUMBER], 
        CONCAT(REPLACE(@t,LEFT(@t,CHARINDEX('_',@t)),''),'_') [REMAINDER],  
        @t AS [Original Number After @num]
    UNION ALL
    SELECT 
        REPLACE(LEFT([REMAINDER],CHARINDEX('_',[REMAINDER])),'_',''), 
        REPLACE([REMAINDER],LEFT([REMAINDER],CHARINDEX('_',[REMAINDER])),''), 
        @t
    FROM CTE 
    WHERE LEN([REMAINDER])>0
)
SELECT NUMBER AS [Ending Numbers] from CTE
SELECT @MatchPos [MatchPos index], REPLACE(substring(@InputString,@MatchPos,3),'_','') AS [MatchPos Number], @num [_N_ INDEX], SUBSTRING(@InputString,@num,LEN(@inputString)-@num) AS [Numbers After _N_]