Split Data in SQL Server String -

3106 points

967 posts split data in sql server string

25 minutes ago|link

hi all,

i have 2 comma separated strings, 1 have control name , other have values have send stored procedure along table name in data needs inserted.

now, how can split these strings , form insert query in sql server 2008 ?

also, have 1 table in have maintained control value should stored in column ?

so need form insert query using ? how can ?

this function split strings:

create function dbo.split(@value varchar(8000),@delim varchar(8000)) returns table return ( select d.value,        d.orders,        ivalue = convert(int, case when isnumeric(d.value)=1 , d.value not '%[^0-9 +-]%' , len(replace(replace(replace(d.value,' ',''),'-',''),'+',''))<=10 case when convert(bigint,d.value) between -2147483648 , 2147483647 d.value end end)              (             select                        value= replace(substring(value,                                             idx,                                             case when cnt>=0 cnt end /* case для защиты от нехороших планов, когда сначала идет вычисление substring, а потом ограничивающее по s_value.number between */                                          )                                  ,char(1),'')                     ,orders=( datalength(left(value,idx-1))-datalength(replace(left(value,idx-1),@delim,''))                             )/datalength(@delim)                 (                        select number                              ,idx                              ,cnt = charindex(@delim,value, number + 1) - number - datalength(@delim)                              ,value                                                             (                                    select number                                          ,idx = number + datalength(@delim)                                          ,value = (select @delim+char(1)+@value+char(1)+@delim)                                       dbo.s_value                                         number between 1 , datalength( (select @delim+char(1)+@value+char(1)+@delim) ) - datalength(@delim)                                  ) t                                       substring(t.value, number, datalength(@delim)) = @delim                               ) t                     ) d           )   go