A "UDF" is a "User Defined Function"... and for this, we don't need it. We're gonna split and display the rows just like they were in atable without using a Cursor, While Loop, or a Function. We are, however, going to need a Tally table (well indexed single column ofsequential numbers). I'm sure that someone will figure out a way to do it in SQL 2k5 without a Tally table, but I don't have 2k5, yet, to test on.Here's how to make a Tally table... it should be made a permanent table in your arsenal of SQL tools because it has lots of utility...--===== Create and populate the Tally table on the fly SELECT TOP 11000 --equates to more than 30 years of dates IDENTITY(INT,1,1) AS N INTO dbo.Tally FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2--===== Add a Primary Key to maximize performance ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100--===== Allow the general public to use it GRANT SELECT ON dbo.Tally TO PUBLIC
Now, some test data to play with... this is NOT part of the solution--=====================================================================================================================-- This section is just test setup. IT IS NOT PART OF THE SOLUTION!!!--=====================================================================================================================-- drop table #yourtable-- drop table #tobesplit--===== Create a test table to hold test data and populate it... -- This is NOT part of the solution... it's just necessary for testing. CREATE TABLE #yourtable (RawData VARCHAR(7998)) INSERT INTO #yourtable(RawData) SELECT 'date=2007-07-01 time=20:29:01 devname=TEST01 device_id=FGT0000000000001 log_id=000000001 type=event01 subtype=auth01 pri=notice01 vd=root01 proto=1 src=xx.xxx.xx.xxx:1051 dst=xx.xxx.xxx.xx:81 adgroup="xxx/WWW_Example" user="username" ui=xx.xxx.xxx.xx:80(6) action=FSAE-auth status=success msg="AD group xxx/WWW_Example user username succeeded in authentication"'UNION ALL SELECT 'date=2007-07-02 time=20:29:02 devname=TEST02 device_id=FGT0000000000002 log_id=000000002 type=event02 subtype=auth02 pri=notice02 vd=root02 proto=2 src=xx.xxx.xx.xxx:1052 dst=xx.xxx.xxx.xx:82 adgroup="xxx/WWW_Example" user="username" ui=xx.xxx.xxx.xx:80(6) action=FSAE-auth status=success msg="AD group xxx/WWW_Example user username succeeded in authentication"'UNION ALL SELECT 'date=2007-07-03 time=20:29:03 devname=TEST03 device_id=FGT0000000000003 log_id=000000003 type=event03 subtype=auth03 pri=notice03 vd=root03 proto=3 src=xx.xxx.xx.xxx:1053 dst=xx.xxx.xxx.xx:83 adgroup="xxx/WWW_Example" user="username" ui=xx.xxx.xxx.xx:80(6) action=FSAE-auth status=success msg="AD group xxx/WWW_Example user username succeeded in authentication"'UNION ALL SELECT 'date=2007-07-04 time=20:29:04 devname=TEST04 device_id=FGT0000000000004 log_id=000000004 type=event04 subtype=auth04 pri=notice04 vd=root04 proto=4 src=xx.xxx.xx.xxx:1054 dst=xx.xxx.xxx.xx:84 adgroup="xxx/WWW_Example" user="username" ui=xx.xxx.xxx.xx:80(6) action=FSAE-auth status=success msg="AD group xxx/WWW_Example user username succeeded in authentication"'UNION ALL SELECT 'date=2007-07-05 time=20:29:05 devname=TEST05 device_id=FGT0000000000005 log_id=000000005 type=event05 subtype=auth05 pri=notice05 vd=root05 proto=5 src=xx.xxx.xx.xxx:1055 dst=xx.xxx.xxx.xx:85 adgroup="xxx/WWW_Example" user="username" ui=xx.xxx.xxx.xx:80(6) action=FSAE-auth status=success msg="AD group xxx/WWW_Example user username succeeded in authentication"'UNION ALL SELECT 'date=2007-07-06 time=20:29:06 devname=TEST06 device_id=FGT0000000000006 log_id=000000006 type=event06 subtype=auth06 pri=notice06 vd=root06 proto=6 src=xx.xxx.xx.xxx:1056 dst=xx.xxx.xxx.xx:86 adgroup="xxx/WWW_Example" user="username" ui=xx.xxx.xxx.xx:80(6) action=FSAE-auth status=success msg="AD group xxx/WWW_Example user username succeeded in authentication"'UNION ALL SELECT 'date=2007-07-07 time=20:29:07 devname=TEST07 device_id=FGT0000000000007 log_id=000000007 type=event07 subtype=auth07 pri=notice07 vd=root07 proto=7 src=xx.xxx.xx.xxx:1057 dst=xx.xxx.xxx.xx:87 adgroup="xxx/WWW_Example" user="username" ui=xx.xxx.xxx.xx:80(6) action=FSAE-auth status=success msg="AD group xxx/WWW_Example user username succeeded in authentication"'UNION ALL SELECT 'date=2007-07-08 time=20:29:08 devname=TEST08 device_id=FGT0000000000008 log_id=000000008 type=event08 subtype=auth08 pri=notice08 vd=root08 proto=8 src=xx.xxx.xx.xxx:1058 dst=xx.xxx.xxx.xx:88 adgroup="xxx/WWW_Example" user="username" ui=xx.xxx.xxx.xx:80(6) action=FSAE-auth status=success msg="AD group xxx/WWW_Example user username succeeded in authentication"'UNION ALL SELECT 'date=2007-07-09 time=20:29:09 devname=TEST09 device_id=FGT0000000000009 log_id=000000009 type=event09 subtype=auth09 pri=notice09 vd=root09 proto=9 src=xx.xxx.xx.xxx:1059 dst=xx.xxx.xxx.xx:89 adgroup="xxx/WWW_Example" user="username" ui=xx.xxx.xxx.xx:80(6) action=FSAE-auth status=success msg="AD group xxx/WWW_Example user username succeeded in authentication"'UNION ALL SELECT 'date=2007-07-10 time=20:29:10 devname=TEST10 device_id=FGT0000000000010 log_id=000000010 type=event10 subtype=auth10 pri=notice10 vd=root10 proto=10 src=xx.xxx.xx.xxx:1060 dst=xx.xxx.xxx.xx:90 adgroup="xxx/WWW_Example" user="username" ui=xx.xxx.xxx.xx:80(6) action=FSAE-auth status=success msg="AD group xxx/WWW_Example user username succeeded in authentication"'
... and, finally... the solution... of course, you'll need to change some table names to make it work for your particular situation, butworks fine as is for this particular demo...--=====================================================================================================================-- Solution starts here...--=====================================================================================================================--===== First, move the data into a temp table so we can number the rows SELECT IDENTITY(INT,1,1) AS RowNum, RawData INTO #ToBeSplit FROM #yourtable--===== This "Cross tab" displays the data as if it were coming from a nice ol' table. -- Could be used to populate a table for real using an insert SELECT RowNum, FullDate = CAST(MAX(CASE WHEN s2.ColName = 'Date' THEN s2.ColData ELSE '' END) + MAX(CASE WHEN s2.ColName = 'Time' THEN s2.ColData ELSE '' END) AS DATETIME), DevName = MAX(CASE WHEN s2.ColName = 'DevName' THEN s2.ColData ELSE '' END), Device_ID = MAX(CASE WHEN s2.ColName = 'Device_ID' THEN s2.ColData ELSE '' END), Log_ID = MAX(CASE WHEN s2.ColName = 'Log_ID' THEN s2.ColData ELSE '' END), Type = MAX(CASE WHEN s2.ColName = 'Type' THEN s2.ColData ELSE '' END), SubType = MAX(CASE WHEN s2.ColName = 'SubType' THEN s2.ColData ELSE '' END), Pri = MAX(CASE WHEN s2.ColName = 'Pri' THEN s2.ColData ELSE '' END), VD = MAX(CASE WHEN s2.ColName = 'VD' THEN s2.ColData ELSE '' END), Proto = MAX(CASE WHEN s2.ColName = 'Proto' THEN s2.ColData ELSE '' END), Src = MAX(CASE WHEN s2.ColName = 'Src' THEN s2.ColData ELSE '' END), Dst = MAX(CASE WHEN s2.ColName = 'Dst' THEN s2.ColData ELSE '' END), AdGroup = MAX(CASE WHEN s2.ColName = 'AdGroup' THEN s2.ColData ELSE '' END), [User] = MAX(CASE WHEN s2.ColName = 'User' THEN s2.ColData ELSE '' END), UI = MAX(CASE WHEN s2.ColName = 'UI' THEN s2.ColData ELSE '' END), [Action] = MAX(CASE WHEN s2.ColName = 'Action' THEN s2.ColData ELSE '' END), Status = MAX(CASE WHEN s2.ColName = 'Status' THEN s2.ColData ELSE '' END), Msg = MAX(CASE WHEN s2.ColName = 'Msg' THEN s2.ColData ELSE '' END) FROM (--==== This splits the column name from the data and gets rid of double quotes SELECT s1.RowNum, ColName = LEFT(REPLACE(LEFT(s1.Split1,CHARINDEX(' ',s1.Split1)),'"',''),CHARINDEX('=',s1.Split1)-1), ColData = CASE WHEN LEFT(s1.Split1,4) = 'msg=' THEN REPLACE(SUBSTRING(s1.Split1,5,8000),'"','') ELSE SUBSTRING(REPLACE(LEFT(s1.Split1,CHARINDEX(' ',s1.Split1)),'"',''),CHARINDEX('=',s1.Split1)+1,8000) END FROM(--===== This does a partial split on each column name (and other stuff in Msg that we'll get rid of later) SELECT tbs.RowNum,SUBSTRING(' '+tbs.RawData,t.N+1,8000) AS Split1 FROM dbo.Tally t CROSS JOIN #ToBeSplit tbs WHERE SUBSTRING(' '+tbs.RawData,t.N,1) = ' ' AND t.N <= LEN(' '+tbs.RawData) )s1 WHERE s1.Split1 LIKE '%=%' --Gets rid of the bad stuff from Msg provided that Msg never has an "=" in it... )s2 GROUP BY s2.RowNumYou can gen the results for yourself...--Jeff Moden 
String Manipulation
Posted - 2007-08-21 : 15:06:02
Thank you VERY much for the great feedback. It's always nice to know if the solutions I (and others) write actually help folks. Glad everything worked out. If you have any questions on how the code works, please don't hesitate to ask.--Jeff Moden