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


i need parsing values in before insert trigger on sql2008 server.

i have table contains text field (lets call source). field value may

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

and want comma separated values in field (lets target):

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

can done in before insert trigger or need external app of sort?

thank you.

first need create table valued function takes start , end value generate sequence.this done using recursive cte

create function fngetrange(@startvalue int,@endvalue int) returns  @rtntable table  (   generatedval varchar(max) ) begin ;with cte(startvalue,rangeval,generatedval) ( select @startvalue,@endvalue,@startvalue generatedval union select  startvalue, rangeval, generatedval+1 cte r  rangeval > generatedval  ) insert @rtntable select  generatedval cte return end

you need split single column rows can range , pass function

 ;with cte(range)   (    select     right(left(t.rangeval,number-1),   charindex(',',reverse(left(','+t.rangeval,number-1)))) range     master..spt_values,   yourtable t     type = 'p' , number between 1 , len(t.rangeval)+1   ,  (substring(t.rangeval,number,1) = ',' or substring(t.rangeval,number,1)  = '')  )

the above solution posted here uses master..spt_values generate sequence

the cte return result 

range 10-15 20-22 25-26

now need split range startvalue , endvalue 

rangecte  (startvalue,endvalue) ( select parsename(replace(range,'-','.'),2) startvalue, parsename(replace(range,'-','.'),1) endvalue cte )

the above rangecte return data like

  startvalue  endvalue     10         15     20         22     25         26

once these values need pass function fngetrange using cross apply

 rowvalue (rangesep)  ( select val.generatedval rangesep rangecte r   cross apply   dbo.fngetrange(r.startvalue,r.endvalue) val )

this generate sequence in multiple rows .to convert single row use xml path

 select stuff( (select ',' + rangesep    rowvalue     xml path(''),type).value('.','varchar(max)'),1,1,'')

now combining cte's final query 

;with cte(range)  (   select    right(left(t.rangeval,number-1),   charindex(',',reverse(left(','+t.rangeval,number-1)))) range     master..spt_values,   yourtable t     type = 'p' , number between 1 , len(t.rangeval)+1   ,  (substring(t.rangeval,number,1) = ',' or substring(t.rangeval,number,1)  = '')  ),rangecte  (startvalue,endvalue)   ( select parsename(replace(range,'-','.'),2) startvalue, parsename(replace(range,'-','.'),1) endvalue cte   ),rowvalue (rangesep)      ( select val.generatedval rangesep rangecte r       cross apply       dbo.fngetrange(r.startvalue,r.endvalue) val     )       select stuff(     (select ',' + rangesep      rowvalue      xml path(''),type).value('.','varchar(max)'),1,1,'')

the result 

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

as others have suggested ,you should change table design .instead of storing string create columns store range of type int

updated

just on same page . create insert trigger on source table contains values 10-15,20-22,25-26.you need convert these values sequence , insert target table.if case can use below code.

basically trigger created derived table inserts data inserted logical tables in trigger.then using above nested cte's ,you insert sequence in target table

create trigger tri_inserts on after insert set nocount on declare @rangetable table (rangeval varchar(max))  insert @rangetable select rangecolumn inserted  ;with cte(range)  (  select   right(left(t.rangeval,number-1),  charindex(',',reverse(left(','+t.rangeval,number-1)))) range   master..spt_values,  @rangetable t   type = 'p' , number between 1 , len(t.rangeval)+1  ,  (substring(t.rangeval,number,1) = ',' or substring(t.rangeval,number,1)  = '')   ),rangecte  (startvalue,endvalue)    (     select parsename(replace(range,'-','.'),2) startvalue,     parsename(replace(range,'-','.'),1) endvalue     cte    ),rowvalue (rangesep)      ( select val.generatedval rangesep rangecte r       cross apply       dbo.fngetrange(r.startvalue,r.endvalue) val     )       insert target(destcolumn)     --change target name       select stuff(      (select ',' + rangesep       rowvalue       xml path(''),type).value('.','varchar(max)'),1,1,'')    go

Comments

Post a Comment

Popular posts from this blog

authentication - Mongodb revoke acccess to connect test database -

i have ec2 instance mongodb 3.2.0 installed. have user admin access. can connect user , perform operation remotely. 1 can connect database test remote if knows ip mongo 11.11.11.11. although not able make operations, want restrict access user credentials can connect server. first need create users , grant roles users, example: use admin db.createuser( { user: "myuseradmin", pwd: "abc123", roles: [ { role: "useradminanydatabase", db: "admin" } ] } ) then need start server --auth parameter mongod --auth more information here
Read more

r - Update two sets of radiobuttons reactively - shiny -

i have read ( how make choices in radiobuttons reactive in shiny? ) shows me how update radiobutton s in reactive way. however, when try , update 2 sets of buttons same data, 1 set renders. example: server: # create example data wafer <- rep(c(1:3), each=3) length <- c(1,1,2,1,1,1,3,5,1) width <- c(3,1,6,1,1,1,1,1,6) dd <- data.frame(wafer, length, width) shinyserver(function(input, output, session){ # create reactive dataframe store data values <- reactivevalues() values$df <- data.frame() # lengths , widths of wafer user input <- eventreactive(input$do, { subset(dd, wafer %in% input$wafer, select = length:width) }) # update reactive data frame lengths , widths have been selected user input observe({ if(!is.null(a())) { values$df <- rbind(isolate(values$df), a()) } }) output$wl <- renderdatatable({ a() }) # update radio buttons unique length , widths stored in values$df # ever "observe" put first in co...
Read more

ios - Realm over CoreData should I use NSFetchedResultController or a Dictionary? -

i'm working on app using core data , nsfetchedresultcontroller many reasons switch use realm. saw there "realm version" of nsfetchedresultcontroller on github wouldn't compatible current code using core data. the view controller displaying list of people same school, address book. list sublist of people studied in same city. so thinking make 1 unique request database retrieve list of people , filter locally list within 1 dictionary per school [string: anyobject] string section name within tableview , anyobject array of people. first solution, make nsfetchedresultcontroller each school predicate filtering location. delete actions etc.. handled delegates -> not compatible realm create dictionaries , update them each actions... -> works realm it's annoying code. any better solution? edit: i need clarify request: i'd write class inherit uitableviewcontroller. this class has list of people sorted in alphabetical order the tableview has ...
Read more