时间戳

首页 > 解决方案 > 使用 PATINDEX 在 SQL Server 中提取子字符串？

sql - 使用 PATINDEX 在 SQL Server 中提取子字符串？

问题描述

我想从 SQL Server 中的字符串中提取一些特定值，但我不确定如何使用PATINDEX.

取这个字符串：

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

我想提取出 500000 的值（对于@RowsPerRun），对于 @RowsPerBatch 的值为 10000，对于 @NbrDaysToKeepRpt 的值为 7。这些值的长度是可变的，所以我不能保证 @RowsPerRun 的值是 6 个字符。

那可能吗？

标签： sqlsql-serverregexpatindex

解决方案

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

回报：

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

请注意，我添加了一些额外的列来帮助您了解正在发生的事情。

更新：对着桌子

这就是您将此逻辑应用于一系列值的方式：

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;

回报：

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

推荐阅读