Jassim Rahma


I have options_field contains a lot of data.. here are examples:

DT,NB,NM,ANI=8008808039139662
ANI=8008808036637576,DNIS=80088080,DT,NB,NM,UNIT
ANI=8008808039188390,ANI=8008808017403125,DNIS=80088080,DT,NB,NM,UNIT
ANI=8008808039653135,ALIAS,DNIS=80088080,DT,NB,NM,UNIT
ALIAS,ANI=8008808039169467,ANI=8008808017326875,UNIT,NM,NB,DT,DNIS=80088080
DT,NB,NM,DNIS=80088080,ANI=8008808036331200,ANI=8008808039318646

I want to extract every 39 and 36 mobile numbers in the ANI. All numbers are 8digits as highlighted ad some records will have more than mobile number while some won't have any mobile number listed. so the result should be

39139662
36637576
39188390
39653135
39169467
36331200 - 39318646





Re: how to extract this text?

Kent Waldrop 2007 Mar


Here is a first pass at what you might be looking for:

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






Re: how to extract this text?

Chris Howarth

Here's a similar approach:

Chris

 

DECLARE @numbers TABLE (Number INT NOT NULL PRIMARY KEY)

INSERT INTO @numbers

SELECT ROW_NUMBER() OVER(ORDER BY s1.[object_id])

FROM master.sys.objects s1

CROSS JOIN master.sys.objects s2

 

DECLARE @Input TABLE

(

[ID] INT NOT NULL IDENTITY PRIMARY KEY,

[String] VARCHAR(150)

)

 

INSERT INTO @Input([String])

SELECT 'DT,NB,NM,ANI=8008808039139662' UNION

SELECT 'ANI=8008808036637576,DNIS=80088080,DT,NB,NM,UNIT' UNION

SELECT 'ANI=8008808039188390,ANI=8008808017403125,DNIS=80088080,DT,NB,NM,UNIT' UNION

SELECT 'ANI=8008808039653135,ALIAS,DNIS=80088080,DT,NB,NM,UNIT' UNION

SELECT 'ALIAS,ANI=8008808039169467,ANI=8008808017326875,UNIT,NM,NB,DT,DNIS=80088080' UNION

SELECT 'DT,NB,NM,DNIS=80088080,ANI=8008808036331200,ANI=8008808039318646'

 

DECLARE @Output TABLE

(

[ID] INT NOT NULL,

[XPOS] INT NOT NULL,

[String] VARCHAR(8) NOT NULL

)

INSERT INTO @Output([ID], [XPOS], [String])

SELECT i.[ID],

n.Number,

CASE WHEN SUBSTRING(i.String, n.Number, 14) LIKE 'ANI=800880803[6 OR 9]' THEN SUBSTRING(i.String, n.Number + 12, 8) END AS [Output]

FROM @Input i

CROSS JOIN @Numbers n

WHERE CASE WHEN SUBSTRING(i.String, n.Number, 14) LIKE 'ANI=800880803[6 OR 9]' THEN SUBSTRING(i.String, n.Number + 12, 8) END IS NOT NULL

 

SELECT o2.[ID],

REPLACE (( SELECT o1.String AS [data()]

FROM @Output o1

WHERE o1.[ID] = o2.[ID]

ORDER BY o1.[id], o1.XPOS

FOR XML PATH ('')), ' ', ' - ') AS String

FROM (SELECT DISTINCT [ID] FROM @Output) o2

ORDER BY o2.[ID]