Parsing an irregularly delimited string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Parsing an irregularly delimited string

Author

Topic

Kanati
Starting Member

36 Posts

Posted - 2010-08-04 : 16:04:31

Specifically... I'm parsing dns logs (MS DNS) which look something like this:

[code]
20100717 22:20:37 928 PACKET 01943580 UDP Rcv 10.16.68.133 a6c1 Q [0001 D NOERROR] SOA (12)ServiceLT114(5)tserv(4)domain(3)com(0)
20100717 22:20:37 928 PACKET 01943580 UDP Snd 10.16.68.133 a6c1 R Q [8085 A DR NOERROR] SOA (12)ServiceLT114(5)tserv(4)domain(3)com(0)
20100717 22:20:39 928 PACKET 01944030 UDP Rcv 10.16.71.217 b93c Q [0001 D NOERROR] A (9)HVProfile(5)tserv(4)domain(3)com(0)
[code]

I've bulk inserted the data into a table, parsed out almost everything I need to excepting the domain information.

The domain info is (#)blah(#)blah(0) the number in parens is the number of characters to expect afterwards. It can be 1 or 2 digits so (_) for patindex won't work (%) will I think....

I obviously can use a cursor/while and drop things into a variable to massage the data and spit it back out, but I'm wondering if there's any way I could do it en-mass... if I could replace (%) with . that would be perfect. But I'm not finding an easy way to do that.

Any help would be appreciated.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-08-04 : 16:24:57

IN RE: if I could replace (%) with . that would be perfect.

Look up REPLACE in BOL.

=======================================
A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007)

Kristen
Test

22859 Posts

Posted - 2010-08-04 : 16:33:49

I don't think REPLACE is going to handle Reg Ex ...

Kristen
Test

22859 Posts

Posted - 2010-08-04 : 16:35:25

My best guess is to split on "(" and then split off the initial leading digits up to the ")" for each of those split items.

Peso will probably have a smart way of doing it though ...

Kristen
Test

22859 Posts

Posted - 2010-08-04 : 16:37:05

Hmmm ... or, just maybe, replace ")" with "(" and then split on "("

(12)ServiceLT114(5)tserv(4)domain(3)com(0)
becomes
(12(ServiceLT114(5(tserv(4(domain(3(com(0(

which splits to:

12 ServiceLT114 5 tserv 4 domain 3 com 0

Kanati
Starting Member

36 Posts

Posted - 2010-08-04 : 17:39:44

quote:
Originally posted by Kristen

Hmmm ... or, just maybe, replace ")" with "(" and then split on "("

(12)ServiceLT114(5)tserv(4)domain(3)com(0)
becomes
(12(ServiceLT114(5(tserv(4(domain(3(com(0(

which splits to:

12 ServiceLT114 5 tserv 4 domain 3 com 0

Hrm....... I didn't think of that. That's a possibility.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-08-04 : 18:51:38

This is a little convoluted but works:

declare @a table (i int not null identity(1,1), data varchar(1024), 
dom as replace(substring(data,charindex(')',data,charindex('(',data))+1,len(data)),'(0)',''))  --computed column removes unneeded data

insert @a(data) 
select '20100717 22:20:37 928 PACKET 01943580 UDP Rcv 10.16.68.133 a6c1 Q [0001 D NOERROR] SOA (12)ServiceLT114(5)tserv(4)domain(3)com(0)' 
union all
select '20100717 22:20:37 928 PACKET 01943580 UDP Snd 10.16.68.133 a6c1 R Q [8085 A DR NOERROR] SOA (12)ServiceLT114(5)tserv(4)domain(3)com(0)' 
union all
select '20100717 22:20:39 928 PACKET 01944030 UDP Rcv 10.16.71.217 b93c Q [0001 D NOERROR] A (9)HVProfile(5)tserv(4)domain(3)com(0)'

;with a(a,i) as (select ')'+dom+'(', i from @a), --add parens to simplify parsing
n(n) as (select 1 union all select n+1 from n where n<1024)  --number generator for substring
select i, stuff((select '.'+substring(a,n,charindex('(',a,n)-n)
			from a cross join n 
			where substring(a,n-1,1)=')' and a.i=b.i  --need to "join" row ID/primary key
			order by i, n
			for xml path('')),1,1,'')  --this will concatenate rows
from a b
option (maxrecursion 1024)

You'll have to modify this for your structure, and you'll need a column that uniquely identifies the row in a join (see comment above)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources