May 3, 2010 at 11:02 am
Sorry...got pulled into some other projects.
I found that the code is working as it should when using a "real" table with thousands of rows. The output looks good, even when the ordering of the strings within the csuriquery column are different.
Now, I'm trying to execute the query from an ASP page and it's giving me grief. The error I'm getting is 'CREATE FUNCTION' must be the first statement in a query batch. The first line of the query is: CREATE FUNCTION DelimitedSplit (
I then thought that creating a stored procedure might work better. I could pass the procedure something like the date and have it return all that match. I've tried a few (several) iterations of code, but can't get the function to work within the stored procedure. From what I've read, this might be impossible or cause performance issues.
So, should I continue the path of using a query in ASP or use a stored procedure? Here is what I have so far:
IF OBJECT_ID('TempDB..#YourTable','U') IS NOT NULL
DROP TABLE #YourTable
;
GO
--===== Create the test table
CREATE TABLE #YourTable
(
Date NCHAR(10) NOT NULL,
Time NCHAR(8) NOT NULL,
CsuriQuery NVARCHAR(2000) NOT NULL
)
;
--===== Populate the test table with test data
INSERT INTO #YourTable
(Date, Time, CsuriQuery)
SELECT '2010-04-08','00:00:02','Cmd=Ping&User=User1_ID&DeviceId=PALM24854ace5ac4e080c918b7213ce4&DeviceType=Palm&Log=V121_LdapC1_LdapL0_RpcC29_RpcL36_Hb1740_Rto1_S1_' UNION ALL
SELECT '2010-04-08','00:00:03','User=User2_ID&DeviceId=Appl84930CUZ3NP&DeviceType=iPhone&Cmd=Ping&Log=V121_Sst20_LdapC0_LdapL0_RpcC57_RpcL63_Hb975_Rto1_Erq1_Pk4026417530_S1_' UNION ALL
SELECT '2010-04-08','00:00:04','NewParameter=Whatever&User=User2_ID&DeviceId=Appl84930CUZ3NP&DeviceType=iPhone&Cmd=Ping&Log=V121_Sst20_LdapC0_LdapL0_RpcC57_RpcL63_Hb975_Rto1_Erq1_Pk4026417530_S1_'
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[DelimitedSplit] (
@list varchar(max),
@Delimiter char(1)
)
RETURNS TABLE
AS
RETURN
WITH Tens (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ),
Thousands (N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),
Millions (N) AS (SELECT 1 FROM Thousands t1 CROSS JOIN Thousands t2),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions),
ItemSplit (ItemOrder, Item) AS (
SELECT N,
RTRIM(LTRIM(SUBSTRING(@Delimiter + @list + @Delimiter,N+1,
CHARINDEX(@Delimiter,@Delimiter + @list + @Delimiter,N+1)-N-1)))
FROM Tally
WHERE N < LEN(@Delimiter + @list + @Delimiter)
AND SUBSTRING(@Delimiter + @list + @Delimiter,N,1) = @Delimiter
)
SELECT ItemID = ROW_NUMBER() OVER (ORDER BY ItemOrder),
Item
FROM ItemSplit
GO
declare @Delimiter char(1)
set @Delimiter = '&'
SELECT date, time,
[Cmd] = MAX(CASE WHEN LEFT(Item,4) = 'Cmd=' THEN SUBSTRING(Item, 5, 50) ELSE NULL END),
[User] = MAX(CASE WHEN LEFT(Item,5) = 'User=' THEN SUBSTRING(Item, 6, 50) ElSE NULL END),
[DeviceID] = MAX(CASE WHEN LEFT(Item,9) = 'DeviceID=' THEN SUBSTRING(Item, 10, 50) ELSE NULL END),
[DeviceType] = MAX(CASE WHEN LEFT(Item, 11) = 'DeviceType=' THEN SUBSTRING(Item, 12, 50) ELSE NULL END),
[LOG] = MAX(CASE WHEN LEFT(Item,4) = 'Log=' THEN SUBSTRING(Item, 5, 50) ELSE NULL END)
FROM #YourTable t1
CROSS APPLY DelimitedSplit(csuriquery, @Delimiter)
GROUP BY date, time
ORDER BY date, time
GO
DROP FUNCTION DelimitedSplit
As always, your help is appreciated!
May 3, 2010 at 11:06 am
Dumb question, but why constantly create and drop the function? Wouldn't it make more sense to just create the function in the database and then call it as needed?
May 3, 2010 at 1:03 pm
That's just some sample script. I believe someone put the DROP statement in there just to clean up.
I did add a UDF called DelimitedSplit, but can't figure out how to call it from within the query. Here is the original line where it's called:
CROSS APPLY DelimitedSplit(csuriquery, @Delimiter)
After creating the function, I tried calling it like this:
CROSS APPLY (SELECT * FROM dbo.DelimitedSplit(csuriquery, @Delimiter))
Got a syntax error. I can run this command successfully:
SELECT * FROM dbo.DelimitedSplit('User=ab1234&DeviceId=Appl84930CUZ3NP&DeviceType=iPhone&Cmd=Ping&Log=V121_Sst20_LdapC0_LdapL0_RpcC57_RpcL63_Hb975_Rto1_Erq1_Pk4026417530_S1_','&')
So I know the function works. I just can't figure out how to call it from the CROSS APPLY line.
May 3, 2010 at 1:58 pm
bill.brazell (5/3/2010)
That's just some sample script. I believe someone put the DROP statement in there just to clean up.I did add a UDF called DelimitedSplit, but can't figure out how to call it from within the query. Here is the original line where it's called:
CROSS APPLY DelimitedSplit(csuriquery, @Delimiter)
After creating the function, I tried calling it like this:
CROSS APPLY (SELECT * FROM dbo.DelimitedSplit(csuriquery, @Delimiter))
Got a syntax error. I can run this command successfully:
SELECT * FROM dbo.DelimitedSplit('User=ab1234&DeviceId=Appl84930CUZ3NP&DeviceType=iPhone&Cmd=Ping&Log=V121_Sst20_LdapC0_LdapL0_RpcC57_RpcL63_Hb975_Rto1_Erq1_Pk4026417530_S1_','&')
So I know the function works. I just can't figure out how to call it from the CROSS APPLY line.
Try this, just like it is used in the sample code:
CROSS APPLY dbo.DelimitedSplit(csuriquery, @Delimiter) ds -- you may need the alias
Viewing 4 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply