sql - 将范围值解析为插入触发器中以逗号分隔的值数组

Question

我需要一些帮助来解析 SQL2008 服务器上插入触发器之前的值。

我有一个包含文本字段的表(我们称之为源)。字段值可能是这样的

10-15,20-22,25-26,

我想在另一个字段中使用逗号分隔值(比如说目标):

10,11,12,13,14,15,20,21,22,25,26,

这可以在插入触发器之前完成，还是我需要某种外部应用程序？

谢谢。

Answer 1

首先你需要创建一个Table Valued function这需要 start和 end生成 sequence 的值.这是使用 recursive cte 完成的

CREATE FUNCTION FnGetRange(@startValue int,@endValue int)
RETURNS  @rtnTable TABLE 
(
  generatedVal VARCHAR(MAX)
)
AS
BEGIN
;with cte(startValue,rangeVal,generatedVal)
as
(
Select @startValue,@endValue,@startValue as generatedVal
union all
Select  startValue, rangeVal, generatedVal+1
from cte r
where  rangeVal > generatedVal 
)
Insert into @rtnTable
Select  generatedVal from cte
return
END

您需要 split single column进入rows这样你就可以获得范围并将其传递给 function

 ;with cte(range) as 
 (
   SELECT 
   RIGHT(LEFT(T.rangeVal,Number-1),
  CHARINDEX(',',REVERSE(LEFT(','+T.rangeVal,Number-1)))) as range
  FROM
  master..spt_values,
  yourTable T
  WHERE
  Type = 'P' AND Number BETWEEN 1 AND LEN(T.rangeVal)+1
  AND
 (SUBSTRING(T.rangeVal,Number,1) = ',' OR SUBSTRING(T.rangeVal,Number,1)  = '') 
)

以上解决方案已发布here它基本上使用 master..spt_values 来生成序列

cte 会返回类似的结果

range
10-15
20-22
25-26

现在你需要split范围为 StartValue和 EndValue

rangeCte  (startValue,endValue) as
(
Select parsename(replace(range,'-','.'),2) as startValue,
parsename(replace(range,'-','.'),1) as endValue
from cte
)

以上rangeCTE将返回类似的数据

  startValue  endValue
    10         15
    20         22
    25         26

获得这些值后，只需将其传递给函数 FnGetRange使用 cross apply

 RowValue (rangeSep) as 
( Select val.generatedVal as rangeSep from rangeCte r
  CROSS APPLY
  dbo.FnGetRange(r.StartValue,r.endValue) AS val
) 

这将生成序列，但它将在多行中。将其转换为单个 row使用 xml path

 SELECT STUFF(
(SELECT ',' + rangeSep
   FROM RowValue
    FOR XML PATH(''),type).value('.','varchar(max)'),1,1,'') 

现在结合所有 CTE's最终查询是

;with cte(range) as 
(
  SELECT 
  RIGHT(LEFT(T.rangeVal,Number-1),
  CHARINDEX(',',REVERSE(LEFT(','+T.rangeVal,Number-1)))) as range
  FROM
  master..spt_values,
  yourTable T
  WHERE
  Type = 'P' AND Number BETWEEN 1 AND LEN(T.rangeVal)+1
  AND
 (SUBSTRING(T.rangeVal,Number,1) = ',' OR SUBSTRING(T.rangeVal,Number,1)  = '') 
),rangeCte  (startValue,endValue) as
  (
Select parsename(replace(range,'-','.'),2) as startValue,
parsename(replace(range,'-','.'),1) as endValue
from cte
  ),RowValue (rangeSep) as 
    ( Select val.generatedVal as rangeSep from rangeCte r
      CROSS APPLY
      dbo.FnGetRange(r.StartValue,r.endValue) AS val
    ) 
     SELECT STUFF(
    (SELECT ',' + rangeSep
     FROM RowValue
     FOR XML PATH(''),type).value('.','varchar(max)'),1,1,'') 

结果会是

 10,11,12,13,14,15,20,21,22,25,26

正如其他人所建议的，您应该认真更改 table design .而不是将其存储为 string创建 columns存储 range类型 int

已更新

只是为了在同一页面上。您创建 Insert Trigger在包含 10-15,20-22,25-26 等值的源表上.你需要将这些值转换成sequence并将其插入 Target表。如果是这种情况，您可以使用以下代码。

基本上触发器已创建 Derived Table从 inserted 中插入数据这是 trigger 中的逻辑表.然后使用上面的nested CTE's , 你插入 sequence在target表格

create trigger tri_inserts on a
after insert
as
set nocount on
Declare @RangeTable table
(rangeVal varchar(max))

Insert into @RangeTable
Select rangeColumn from INSERTED

;with cte(range) as 
(
 SELECT 
 RIGHT(LEFT(T.rangeVal,Number-1),
 CHARINDEX(',',REVERSE(LEFT(','+T.rangeVal,Number-1)))) as range
 FROM
 master..spt_values,
 @RangeTable T
 WHERE
 Type = 'P' AND Number BETWEEN 1 AND LEN(T.rangeVal)+1
 AND
 (SUBSTRING(T.rangeVal,Number,1) = ',' OR SUBSTRING(T.rangeVal,Number,1)  = '') 
 ),rangeCte  (startValue,endValue) as
   (
    Select parsename(replace(range,'-','.'),2) as startValue,
    parsename(replace(range,'-','.'),1) as endValue
    from cte
   ),RowValue (rangeSep) as 
    ( Select val.generatedVal as rangeSep from rangeCte r
      CROSS APPLY
      dbo.FnGetRange(r.StartValue,r.endValue) AS val
    ) 
     Insert into Target(DestColumn)     --Change the target name 
     SELECT STUFF(
     (SELECT ',' + rangeSep
      FROM RowValue
      FOR XML PATH(''),type).value('.','varchar(max)'),1,1,'') 
  GO