数据库中数据格式:
Date Time Priority HostName Message
04-23-2010 09:51:41 Local7.Debug 172.16.88.10 <PNB0>tvkoo.udp 1272015958-1272016133 172.16.10.23:1167-220.167.212.11:52600 46597 1260
04-23-2010 09:51:41 Local7.Debug 172.16.88.10 <PNB0>default.udp 1272016012-1272016133 172.17.36.72:6048-221.6.235.6:22757 42 0
04-23-2010 09:51:41 Local7.Debug 172.16.88.10 <PNB0>default.udp 1272016012-1272016133 172.17.36.72:6051-124.160.125.5:1814 41 0
04-23-2010 09:51:41 Local7.Debug 172.16.88.10 <PNB0>http.tcp 1272016068-1272016133 172.16.126.158:2438-119.42.233.243:80 970 2541
04-23-2010 09:51:55 Local7.Debug 172.16.88.10 <PNB0>1272153431 172.16.1.133 GET http://nc.qzone.qq.com/cgi-bin/cgi\_farm\_index?mod=user&act=run&ownerId=472193380&uinX=472764098
我想按照Message字段的空格转换成这种格式(Priority和HostName字段最好舍弃了):
Date Time Priority HostName Message_A Message_B Message_C Message_D Message_E
04-23-2010 09:51:41 Local7.Debug 172.16.88.10 <PNB0>tvkoo.udp 1272015958-1272016133 172.16.10.23:1167-220.167.212.11:52600 46597 1260
04-23-2010 09:51:41 Local7.Debug 172.16.88.10 <PNB0>default.udp 1272016012-1272016133 172.17.36.72:6048-221.6.235.6:22757 42 0
04-23-2010 09:51:41 Local7.Debug 172.16.88.10 <PNB0>default.udp 1272016012-1272016133 172.17.36.72:6051-124.160.125.5:1814 41 0
04-23-2010 09:51:41 Local7.Debug 172.16.88.10 <PNB0>http.tcp 1272016068-1272016133 172.16.126.158:2438-119.42.233.243:80 970 2541
04-23-2010 09:51:55 Local7.Debug 172.16.88.10 <PNB0>1272153431 172.16.1.133 GET http://nc.qzone.qq.com/cgi-bin/cgi\_farm\_index?mod=user&act=run&ownerId=472193380&uinX=472764098求sql
Date Time Priority HostName Message
04-23-2010 09:51:41 Local7.Debug 172.16.88.10 <PNB0>tvkoo.udp 1272015958-1272016133 172.16.10.23:1167-220.167.212.11:52600 46597 1260
04-23-2010 09:51:41 Local7.Debug 172.16.88.10 <PNB0>default.udp 1272016012-1272016133 172.17.36.72:6048-221.6.235.6:22757 42 0
04-23-2010 09:51:41 Local7.Debug 172.16.88.10 <PNB0>default.udp 1272016012-1272016133 172.17.36.72:6051-124.160.125.5:1814 41 0
04-23-2010 09:51:41 Local7.Debug 172.16.88.10 <PNB0>http.tcp 1272016068-1272016133 172.16.126.158:2438-119.42.233.243:80 970 2541
04-23-2010 09:51:55 Local7.Debug 172.16.88.10 <PNB0>1272153431 172.16.1.133 GET http://nc.qzone.qq.com/cgi-bin/cgi\_farm\_index?mod=user&act=run&ownerId=472193380&uinX=472764098
我想按照Message字段的空格转换成这种格式(Priority和HostName字段最好舍弃了):
Date Time Priority HostName Message_A Message_B Message_C Message_D Message_E
04-23-2010 09:51:41 Local7.Debug 172.16.88.10 <PNB0>tvkoo.udp 1272015958-1272016133 172.16.10.23:1167-220.167.212.11:52600 46597 1260
04-23-2010 09:51:41 Local7.Debug 172.16.88.10 <PNB0>default.udp 1272016012-1272016133 172.17.36.72:6048-221.6.235.6:22757 42 0
04-23-2010 09:51:41 Local7.Debug 172.16.88.10 <PNB0>default.udp 1272016012-1272016133 172.17.36.72:6051-124.160.125.5:1814 41 0
04-23-2010 09:51:41 Local7.Debug 172.16.88.10 <PNB0>http.tcp 1272016068-1272016133 172.16.126.158:2438-119.42.233.243:80 970 2541
04-23-2010 09:51:55 Local7.Debug 172.16.88.10 <PNB0>1272153431 172.16.1.133 GET http://nc.qzone.qq.com/cgi-bin/cgi\_farm\_index?mod=user&act=run&ownerId=472193380&uinX=472764098求sql
drop function [dbo].[f_GetStr]
GO--分段截取函数(邹建)
CREATE FUNCTION dbo.f_GetStr(
@s varchar(8000), --包含多个数据项的字符串
@pos int, --要获取的数据项的位置
@split varchar(10) --数据分隔符
)RETURNS varchar(100)
AS
BEGIN
IF @s IS NULL RETURN(NULL)
DECLARE @splitlen int
SELECT @splitlen=LEN(@split+'a')-2
WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0
SELECT @pos=@pos-1,
@s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'')
RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),''))
END
GO比较乱,参考一下这个函数,可以分段截取字符串,看能否派上用场