Use PATINDEX to extract a substring in SQL Server?

I have some specific values I want to extract out of a string in SQL Server, but I’m not sure exactly how to get it done with PATINDEX.

Take this string:

declare @Command nvarchar(500) = 'IF dbo.SomeFunctionFn() = 1  BEGIN  EXEC SomeStoredProcPR @RowsPerRun=500000, @RowsPerBatch=10000, @NbrDaysToKeepRpt=7   END'

I want to extract out the values of 500000 (for @RowsPerRun), 10000 for @RowsPerBatch and the value of 7 for @NbrDaysToKeepRpt. The values will be of variable length, so I can’t guarantee the @RowsPerRun value will be 6 characters.

Is that possible?

Answer

DECLARE @Command NVARCHAR(500) = 
  'IF dbo.SomeFunctionFn() = 1  BEGIN  EXEC SomeStoredProcPR @RowsPerRun=500000, @RowsPerBatch=10000, @NbrDaysToKeepRpt=7   END'

SELECT 
  SearchItem = srch.Txt,
  ItemIndex  = st.Pos,
  ItemLen    = t.Ln,
  Item       = SUBSTRING(pfx.Txt,1,t.Ln)
FROM        (VALUES('@RowsPerRun='),('@RowsPerBatch='),('@NbrDaysToKeepRpt=')) AS srch(Txt)
CROSS APPLY (VALUES(CHARINDEX(srch.Txt,@Command),LEN(srch.Txt)))               AS st(Pos,Ln)
CROSS APPLY (VALUES(SUBSTRING(@Command, st.Pos+st.Ln, 500)))                   AS pfx(Txt)
CROSS APPLY (VALUES(PATINDEX('%[^0-9]%',pfx.Txt)-1))                           AS t(Ln);

Returns:

SearchItem         ItemIndex   ItemLen     Item
------------------ ----------- ----------- --------
@RowsPerRun=       59          6           500000
@RowsPerBatch=     79          5           10000
@NbrDaysToKeepRpt= 100         1           7

Note that I included a few extra columns to help you understand what’s happening.

Update: Against a table

This is how you would apply this logic to a series of values:

DECLARE @sometable TABLE (CommandId INT IDENTITY, Command NVARCHAR(500));
INSERT @sometable (Command)
VALUES
('IF dbo.SomeFunctionFn() = 1  BEGIN  EXEC SomeStoredProcPR @RowsPerRun=500000, @RowsPerBatch=10000, @NbrDaysToKeepRpt=7 END'),
('IF dbo.SomeFunctionFn() = 5  BEGIN  EXEC SomeStoredProcPR @RowsPerRun=123, @RowsPerBatch=500, @NbrDaysToKeepRpt=20 END'),
('IF dbo.SomeFunctionFn() = 5  BEGIN  EXEC XXX @RowsPerRun=43, @RowsPerBatch=1000, @NbrDaysToKeepRpt=120 END'),
('IF dbo.SomeFunctionFn() = 5  BEGIN  EXEC abc.yyy @RowsPerRun=43,     @RowsPerBatch=1000, @NbrDaysToKeepRpt=120 END');

SELECT t.CommandId, f.SearchItem, f.Item
FROM @sometable AS t
CROSS APPLY
(
    SELECT 
      SearchItem = srch.Txt,
      ItemIndex  = st.Pos,
      ItemLen    = t.Ln,
      Item       = SUBSTRING(pfx.Txt,1,t.Ln)
    FROM        (VALUES('@RowsPerRun='),('@RowsPerBatch='),('@NbrDaysToKeepRpt=')) AS srch(Txt)
    CROSS APPLY (VALUES(CHARINDEX(srch.Txt,t.Command),LEN(srch.Txt)))              AS st(Pos,Ln)
    CROSS APPLY (VALUES(SUBSTRING(t.Command, st.Pos+st.Ln, 500)))                  AS pfx(Txt)
    CROSS APPLY (VALUES(PATINDEX('%[^0-9]%',pfx.Txt)-1))                           AS t(Ln)
) AS f;

Returns:

CommandId   SearchItem         Item
----------- ------------------ --------
1           @RowsPerRun=       500000
1           @RowsPerBatch=     10000
1           @NbrDaysToKeepRpt= 7
2           @RowsPerRun=       123
2           @RowsPerBatch=     500
2           @NbrDaysToKeepRpt= 20
3           @RowsPerRun=       43
3           @RowsPerBatch=     1000
3           @NbrDaysToKeepRpt= 120
4           @RowsPerRun=       43
4           @RowsPerBatch=     1000
4           @NbrDaysToKeepRpt= 120