Parsing range values to comma separated array of values in insert trigger

Question

I need some help parsing values in before insert trigger on SQL2008 server.

I have a table that contains a text field (lets call it source). The field value may look like this

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

And I want comma separated values in another field (lets say target):

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

Can this be done in before insert trigger or do i need an external app of some sort?

Thank you.

This is doable if you write your own function of course but it is a bit weird IMO, could you explain the logic of this application and maybe we could suggest a better approach — jazzytomato, Mar 25 '13 at 12:43

score 1 · Accepted Answer · edited May 23 '17 at 12:04

First you need to create a Table Valued function which takes start and end value to generate the sequence.This is done using 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

You need to split the single column into rows so that you can get the range and pass it to the 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)  = '') 
)

The above solution was posted here which basically uses master..spt_values to generate the sequence

The cte will return the result like

range
10-15
20-22
25-26

Now you need to split the range into StartValue and EndValue

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

The above rangeCTE will return data like

  startValue  endValue
    10         15
    20         22
    25         26

Once you get these values you just need to pass it to the function FnGetRange using cross apply

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

This will generate the sequence but it will be in multiple rows .To convert it into Single row use xml path

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

Now combining all the CTE's the final query is

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

The result will be

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

As others have suggested ,you should seriously change the table design .Instead of storing it as a string create columns to store the range of type int

Updated

Just to be on the same page . You create Insert Trigger on the source table which contains values like 10-15,20-22,25-26.You need to convert these values into sequence and insert it into Target table.If this is the case you can use the below code.

Basically the trigger created Derived Table which inserts data from the inserted which are logical tables in trigger.Then using the above nested CTE's ,you insert the sequence in the target table

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

praveen this is great stuff. Since you put a great effort into this (and i deeply thank you for it) could you help me put this code in AFTER INSERT trigger? Lets say the SOURCE column holds the original values and TARGET column should hold the comma separated values? Also im having some problems understanding the WHERE clause in the first CTE (type = P...) — no9, Mar 26 '13 at 07:17
There are different types like 'A,B,D ,P and so on' in the table `master..spt_values` . But the numbers are not in any order for these associated types but for `Type = P` we have numbers displayed in the correct order from `0 to 2047` .But instead of using all these numbers im getting only those numbers which lie between `1 and length of the string value` — praveen, Mar 26 '13 at 07:30
This Where clause `(SUBSTRING(T.rangeVal,Number,1) = ',' OR SUBSTRING(T.rangeVal,Number,1) = '')` actually gets the numbers when the expression encounters `- or ''` . That is when it reads `10-15` .The moment it encounters `-` ,it extracts the numbers to the right from the expression `-` — praveen, Mar 26 '13 at 07:32
thanks forall the help! Just one more thing ... what does control the commas in the source field. Lets say the difference between "10-20,20-30" and "10-20,20-30," (note the ending comma). — no9, Mar 26 '13 at 07:51
in the `RowValue` CTE while passing the value to function just replace the code from r.endValue to `replace(r.endValue,',','')` so the function call will be `dbo.FnGetRange(r.StartValue,replace(r.endValue,',',''))` — praveen, Mar 26 '13 at 07:57
@no9 : If the answer helped you kindly mark it so that others might find it benficial — praveen, Mar 26 '13 at 10:30
praveen: I had to change some of the things, so i kindly ask you to edit your answer. I had to change the insert into to update, because its after insert and i had to update the inserted column (update Target set DestColumn = (SELECT STUFF( (SELECT ',' + rangeSep FROM RowValue FOR XML PATH(''),type).value('.','varchar(max)'),1,1,'')) WHERE ID = (SELECT ID from INSERTED)). Then i had to add MAXRECURSION to the function, since i was getting an error when the function had to process larger amount of data (Select generatedVal from cte OPTION (MAXRECURSION 0)). — no9, Apr 09 '13 at 11:36
@no9 So after you change it to `Update` is it working for you ? — praveen, Apr 09 '13 at 11:39
that is correct! Again praveen thank you for an awesome answer. You put alot of effort into this and i thank you from my heart. You saved me alot of time and I owe you a favour! — no9, Apr 10 '13 at 21:34

score 0 · Answer 2 · answered Mar 25 '13 at 12:50

I'd recommend these numbers should really be stored in a separate table e.g. Range (start int, end int) with a many-1 link to the table your referring to which should make queries like this much simpler and save you other potential headaches, but maybe you have good reason for doing it this way. In which case i would suggest creating a UDF to generate the CSV string and declaring the field as a computed column referencing the UDF. Be aware of potential performance issues though if this is a heavily used or frequently updated table.

Hello YoungBob. unfortunately its a closed system (not mine) without any access to the source code. So i need to do this via insert trigger only. — no9, Mar 26 '13 at 07:48

Parsing range values to comma separated array of values in insert trigger

2 Answers2