October 13, 2015 at 4:52 am
I simply need to separate the string for each row by the & character and then I'm assuming i ll be able to COUNT and GROUP BY the occurrences of each separate value in order to find the most commonly used inputs.
I have a column Variables in the table Functions, that contains a string of values separated by the & character that shows the inputs each student inserted into a function.
How would I go about splitting that string without the use of a function or stored procedure and the find the most commonly used variables? (I was thinking the latter part could be easily solved with a COUNT(*) and appropriate GROUP BY.)
Example of data:
StudentID FunctionName Variables
1 Example1 Var1=10&Var2=xy&Sign=True&Role=False
October 13, 2015 at 8:25 am
October 13, 2015 at 8:52 am
I have to split a string with '&' separated values without the use of a function or stored proc
Why can't you use a function? There's a great splitter function that it's very well tested and documented, you could try to replicate the functionality without the function, but that's just getting the possibility of breaking it.
Example:
CREATE TABLE #SampleData(
StudentID int,
FunctionName varchar(20),
Variables varchar(8000)
);
INSERT INTO #SampleData
VALUES( 1, 'Example1', 'Var1=10&Var2=xy&Sign=True&Role=False');
SELECT v.variable,
COUNT(*) AS varcount
FROM #SampleData d
CROSS APPLY dbo.DelimitedSplit8K( d.Variables, '&') s
CROSS APPLY (SELECT LEFT( s.Item, CHARINDEX( '=', s.Item + '=') - 1)) v(variable)
GROUP BY v.variable;
GO
DROP TABLE #SampleData;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply