declare @sizeLimit integer
set @sizeLimit = 80
declare @mockup table
( rid integer identity primary key,
theData varchar (80)
)
insert into @mockup values ('DT,NB,NM,ANI=8008808039139662' )
insert into @mockup values ('ANI=8008808036637576,DNIS=80088080,DT,NB,NM,UNIT' )
insert into @mockup values ('ANI=8008808039188390,ANI=8008808017403125,DNIS=80088080,DT,NB,NM,UNIT' )
insert into @mockup values ('ANI=8008808039653135,ALIAS,DNIS=80088080,DT,NB,NM,UNIT' )
insert into @mockup values ('ALIAS,ANI=8008808039169467,ANI=8008808017326875,UNIT,NM,NB,DT,DNIS=80088080' )
insert into @mockup values ('DT,NB,NM,DNIS=80088080,ANI=8008808036331200,ANI=8008808039318646' )
declare @aniList table
( rid integer,
startLoc integer,
iter integer,
targetString varchar(10),
primary key (rid, startLoc, iter)
)
insert into @aniList
select rid,
startLoc,
n.iter,
substring (targetString, 8*n.iter - 7, 8) as targetString
from ( select rid,
i.iter as startLoc,
min (j.iter) - i.iter - 4 as aniLength,
substring (theData, i.iter + 4, @sizeLimit) as targetString
from @mockup
inner join small_iterator (nolock) i
on i.iter <= @sizeLimit
and substring (theData, i.iter, 4) = 'ANI='
inner join small_iterator (nolock) j
on j.iter >= i.iter + 4
and j.iter <= @sizeLimit
and charindex (substring (theData, j.iter, 1), '0123456789') = 0
group by rid,
i.iter,
substring (theData, i.iter + 4, @sizeLimit)
) a
inner join small_iterator (nolock) n
on n.iter <= (aniLength + 7) / 8
and substring (targetString, 8*n.iter - 7, 2) in ('39', '36')
order by rid, startLoc, n.iter
select distinct
rid,
replace (
( select targetString as [data()]
from @aniList s
where s.rid = a.rid
order by s.rid, s.startLoc, s.iter
for xml path ('')
), ' ', '-') as targetString
from @aniList a
group by rid
order by rid
-- rid targetString
-- ----------- -----------------
-- 1 39139662
-- 2 36637576
-- 3 39188390
-- 4 39653135
-- 5 39169467
-- 6 36331200-39318646