October 24, 2008 at 1:49 am
I have a log table, that contains a Detail column which looks like this...
auth=2 ms,xslt=110 ms,wallet=531 ms,resp=136 ms,total=0.823 sec,meta-data=3 ms
i need to extract each integer with the corresponding name, into new columns so i can
do time calculations on it...
AUTH XSLT Wallet
2 110 531
etc...
any one got any ideas?
October 24, 2008 at 2:34 am
You asking for standard Split functionality... There are many threads (and articles) on this site dealing with this)
A search on "split" should give you the result you want
Sorry I do not have a direct link to a thread
October 24, 2008 at 2:39 am
yisaaacs (10/24/2008)
I have a log table, that contains a Detail column which looks like this...auth=2 ms,xslt=110 ms,wallet=531 ms,resp=136 ms,total=0.823 sec,meta-data=3 ms
i need to extract each integer with the corresponding name, into new columns so i can
do time calculations on it...
AUTH XSLT Wallet
2 110 531
etc...
any one got any ideas?
This takes a nvarchar delimeted list and delimeter and returns a table with the values split on delimeter.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[UTILfn_Split]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[UTILfn_Split]
GO
create function dbo.UTILfn_Split(
@String nvarchar (4000),
@Delimiter nvarchar (10)
)
returns @ValueTable table ([Value] nvarchar(4000))
begin
declare @NextString nvarchar(4000)
declare @Pos int
declare @NextPos int
declare @CommaCheck nvarchar(1)
--Initialize
set @NextString = ''
set @CommaCheck = right(@String,1)
--Check for trailing Comma, if not exists, INSERT
--if (@CommaCheck <> @Delimiter )
set @String = @String + @Delimiter
--Get position of first Comma
set @Pos = charindex(@Delimiter,@String)
set @NextPos = 1
--Loop while there is still a comma in the String of levels
while (@pos <> 0)
begin
set @NextString = substring(@String,1,@Pos - 1)
insert into @ValueTable ( [Value]) Values (@NextString)
set @String = substring(@String,@pos +1,len(@String))
set @NextPos = @Pos
set @pos = charindex(@Delimiter,@String)
end
return
end
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
October 25, 2008 at 5:56 pm
yisaaacs (10/24/2008)
I have a log table, that contains a Detail column which looks like this...auth=2 ms,xslt=110 ms,wallet=531 ms,resp=136 ms,total=0.823 sec,meta-data=3 ms
i need to extract each integer with the corresponding name, into new columns so i can
do time calculations on it...
AUTH XSLT Wallet
2 110 531
etc...
any one got any ideas?
Heh... yep, I do... without loops and no need for a split because of the name/value pairs you have in the data. And, notice, this code get's the correct data no matter what the order of the name/value pairs are... test data is included...
--===== Setup some test data... notice that this is NOT part of the solution.
DECLARE @TestTable TABLE (TestString VARCHAR(8000))
INSERT INTO @TestTable (TestString)
SELECT 'auth=1 ms,xslt=100 ms,wallet=1000 ms,resp=136 ms,total=0.823 sec,meta-data=3 ms' UNION ALL
SELECT 'wallet=2000 ms,resp=136 ms,total=0.823 sec,meta-data=3 ms,auth=2 ms,xslt=200 ms' UNION ALL
SELECT 'resp=136 ms,xslt=300 ms,total=0.823 sec,wallet=3000 ms,auth=3 ms,meta-data=3 ms'
--===== Do the "split"...
SELECT CAST(LEFT(Auth ,CHARINDEX(' ',Auth) -1) AS INT) AS Auth,
CAST(LEFT(Xslt ,CHARINDEX(' ',Xslt) -1) AS INT) AS Xslt,
CAST(LEFT(Wallet,CHARINDEX(' ',Wallet)-1) AS INT) AS Wallet
FROM (SELECT SUBSTRING(TestString,CHARINDEX('auth=' ,TestString)+5,8000) AS Auth,
SUBSTRING(TestString,CHARINDEX('xslt=' ,TestString)+5,8000) AS Xslt,
SUBSTRING(TestString,CHARINDEX('wallet=',TestString)+7,8000) AS Wallet
FROM @TestTable) d
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply