Splitting text string in SQL2K into multiple columns

[GregVM]

I'm creating a web-based NT RAS report site and am looking for the most efficient way to import the data from NT Event log into SQL2k while formatting the data properly. I'm using the 'dumpel' utility from rsc kit and all is fine except the 10th column - the message detail:

"The user DOMAIN\userid connected on port Mdm15 on 08/23/2002 at 07:25am and disconnected on 08/23/2002 at 07:27am. The user was active for 2 minutes 23 seconds. 78809 bytes were sent and 50675 bytes were received. The port speed was 49300."

I need to parse this one long text string into 6 distinct columns: userID, port, duration, bytes_xmt, bytes_rcv and portspeed. Afterwards I'd like to have these values inserted into a new table.

Here's where I'm at with the example string (thanks to other forum notes):
==================================================
--declare string variables
declare @mystring varchar(1000)
declare @length int
declare @len1 int
declare @len2 int

set @mystring = 'The user DOMAIN\userid connected on port Mdm15 on 08/23/2002 at 07:25am and disconnected on
08/23/2002 at 07:27am. The user was active for 2 minutes 23 seconds. 78809 bytes were sent and 50675 bytes were received. The port speed was 49300.'

set @length = len(@mystring)

--print UserID
set @len1 = charindex('user',@mystring,1)
set @len2 = charindex('connected',@mystring,@len1)
print 'UserID:' + rtrim(ltrim(replace(substring(@mystring,@len1,@len2-@len1),'user','')))

--print Port Number
set @len1 = charindex('port',@mystring,1)
set @len2 = charindex('on',@mystring,(@len1))
print 'Port Number:' + rtrim(ltrim(replace(substring(@mystring,@len1,@len2-@len1),'port','')))

--Print Duration
set @len1 = charindex('user was active for ',@mystring,1)
set @len2 = charindex('.',@mystring,(@len1))
print 'Duration:' + rtrim(ltrim(replace(substring(@mystring,@len1,@len2-@len1),'user was active for','')))

--Print Bytes Sent
set @len1 = charindex('. ',@mystring,@len1)
set @len2 = charindex(' bytes',@mystring,(@len1))
print 'Bytes Sent:' + rtrim(ltrim(replace(substring(@mystring,@len1,@len2-@len1),'. ','')))

--Print Bytes Received
set @len1 = charindex('were sent and ',@mystring,@len1)
set @len2 = charindex(' bytes',@mystring,(@len1))
print 'Bytes Rcv:' + rtrim(ltrim(replace(substring(@mystring,@len1,@len2-@len1-1),'were sent and','')))

--Print Port Speed
set @len1 = charindex('port speed was',@mystring,@len1)
set @len2 = charindex('.',@mystring,(@len1))
print 'Port Speed:' + rtrim(ltrim(replace(substring(@mystring,@len1,@len2-@len1),'port speed was','')))
=====================================================

The above code returns the necessary values and now I need to insert these into a new table along with a few other columns from the event dump (date, time, servername). I've heard of using cursors to aid in the extraction and insert, but most forum entries advise not to use cursors for fear of performance degradation issues. Any thoughts?? Any help or script notes would be very appreciated

TIA
-Greg

 

[cheyney]

How do you plan on initially getting the string(s) into your stored procedure? I'm talking about this string:

"The user DOMAIN\userid connected on port Mdm15 on 08/23/2002 at 07:25am and disconnected on 08/23/2002 at 07:27am. The user was active for 2 minutes 23 seconds. 78809 bytes were sent and 50675 bytes were received. The port speed was 49300."

if you have many of these and want to avoid cursors, you could import the strings into column textstring in a temp table tblTemp

then you could do something like this (i will only include the code to insert the UserID and you can fill in the rest):

INSERT INTO tblEvent (UserID)
SELECT rtrim(ltrim(replace(substring(A.textstring,charindex('user',A.textstring,1)
,charindex('connected',A.textstring,charindex('user',A.textstring,1)
)-charindex('user',A.textstring,1)
),'user',''))) As UserID
FROM tblTemp A


what i've basically done is substitue code to eliminate the variables. This way you can insert using a select statement and avoid using a cursor to iterate through your rows.

Not sure if this is what you were asking for. Let me know,

cheyney

 

[GregVM]

Cheyney,

That's pretty much what I'm looking to do. I have a DTS package that grabs the text file from the dumpel utility and imports it on an hourly basis. So far the table has about 5000 records since we've started the procedure. Here's how one complete record looks (tab delimited):
===============================================
1 8/23/2002 07:27:57 20050 Information LANEDGE1 The user domain\tstusr connected on port Mdm15 on 08/23/2002 at 07:25am and disconnected on 08/23/2002 at 07:27am. The user was active for 2 minutes 23 seconds. 78809 bytes were sent and 50675 bytes were received. The port speed was 49300. The reason for
===============================================
I want to grab the date, time, server name (lanedge1 as example) and then parse the last column (message) into 6 new columns. I'll work with the sp you started above, but I have on e question: do I need to create separate INSERT INTO statments for each new column? Looks like the one above is only for the userid.

thanks bunches!
-Greg

 

[cheyney]

nope, one insert for all columns...

heres one that does the ServerName, UserID and the PortNumber:

INSERT INTO tblEvent (ServerName, UserID, PortNumber)
SELECT

ServerName,

rtrim(ltrim(replace(substring(A.textstring,charindex('user',A.textstring,1)
,charindex('connected',A.textstring,charindex('user',A.textstring,1)
)-charindex('user',A.textstring,1)
),'user',''))) As UserID,

rtrim(ltrim(replace(substring(A.textstring,charindex('port',A.textstring,1),charindex('on',A.textstring,(charindex('port',A.textstring,1)))-charindex('port',A.textstring,1)),'port',''))) As PortNumber

FROM tblTemp A

 

[GregVM]

Cheyney, that's working SWEEEET! .... thanks a million!!
-Greg