June 2, 2010 at 10:39 am
If you have a link to an artcile, post or anything on how tod this thats fine; any help is appreciated. I tried a quick search but got zip.
I'm looking for an already existing method (UDF, SP, whatever) that works in T-SQL that will take a string of Key-Value or Name-Value pairs and parse them out.
For example, it woudl take a string of Name-Value pairs like this:
'&ACCT=100&USER=Steve.Jones&PERIOD FROM=01/01/2010&PERIOD TO=05/01/2010&LEVEL=Detail&SORT BY ?=Date'
And parse them out into a 2 column table where the first column is the Name and teh secodn is the value.
or something similiar. I'm just looking for a way to take a '&' delimited set of Name-Value pairs and parse them in some way so the result is more feasable to work with.
My end goal is to use this to check report requests made in the system and when a request contains a string of Name-Value pairs where one of the Names has no value, where the right side of the '=' character has no value before the next ';' or before the end of the string (i.e. &ACCT=;&USER=Steve.Jones) to then stop that request.
We rae having a high occurrence of users making Report requests that are boggingd own the system because (out of laziness or forgetfullness) they are leaving one or more details unspecified.
I can easily do this in VB I just have never messed with it in T-SQL.
Again, any help is well appreciated.
Thanks
Kindest Regards,
Just say No to Facebook!June 2, 2010 at 12:52 pm
Now I'll say first off I'm sure there are more efficient ways to do this with tally tables, etc ... so if you're looking at a large amount of data where performance is going to be an issue you may want to pursue another avenue. However, below is the quick way I would do this in my environment.
I already have a function for general purpose string splitting so I'm using it:
CREATE FUNCTION [dbo].[fn_convertStrListToTable]
(
@delimiter varchar(1),
@list ntext
)
RETURNS @tbl TABLE
(
listpos int IDENTITY(1, 1) NOT NULL,
string nvarchar(4000) NOT NULL
)
AS
BEGIN
declare
@pos int,
@textpos int,
@chunklen smallint,
@STR nvarchar(4000),
@tmpstr nvarchar(4000),
@leftover nvarchar(4000)
set @textpos = 1
set @leftover = ''
while @textpos <= datalength(@list) / 2
begin
set @chunklen = 4000 - datalength(@leftover) / 2
set @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen))
set @textpos = @textpos + @chunklen
set @pos = charindex(@delimiter, @tmpstr)
while @pos > 0
begin
set @STR = substring(@tmpstr, 1, @pos - 1)
insert @tbl (string) values(@str)
set @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
set @pos = charindex(@delimiter, @tmpstr)
end
set @leftover = @tmpstr
end
if ltrim(rtrim(@leftover)) <> ''
insert @tbl (string) values(@leftover)
return
END
So then to get the data you want with the above function in place:
with cteTemp (value)
as
(
select string
FROM master.dbo.fn_convertStrListToTable ('&', @vc_test)
)
select = LEFT(value, charindex('=', value) - 1),
[value] = STUFF(value, 1, charindex('=', value), '')
from cteTemp
where CHARINDEX('=', value) > 0
You would have to modify it with exactly how you handle semicolons, question marks, etc. Hopefully, though, this gives you a basis to get what you want.
June 2, 2010 at 12:54 pm
June 2, 2010 at 1:15 pm
The only thing I would change in bteraberry's solution is the split string function. I would use the DelimitedSplit8K function (search this site for the string) to avoid the while loop.
Other than that: I second bteraberry's approach.
June 2, 2010 at 1:22 pm
Thanks Imu92 & bteraberry for giving me some starting points
Kindest Regards,
Just say No to Facebook!June 2, 2010 at 2:19 pm
lmu92 (6/2/2010)
The only thing I would change in bteraberry's solution is the split string function. I would use the DelimitedSplit8K function (search this site for the string) to avoid the while loop.Other than that: I second bteraberry's approach.
I totally agree. The function was in place (on all of our servers) when I got here so I use it, but I would like to modify it to make it perform better ... looping sucks. Generally, we're only using it for strings with fewer than 5 values, so the performance doesn't really have an impact but it's a needless loss.
I'll be taking a look at that DelimitedSplit8K function as well to see if it's something we can implement. My biggest concern is that we still have a few 2k boxes and as it is right now, all the udf's are identical on all the boxes.
Thanks Lutz!
June 2, 2010 at 2:39 pm
@bteraberry:
I guess the biggest challenge would be to come up with a replacement for ROW_NUMBER().
To substitute UNPIVOT and the CTE is a lot easier to handle...
Maybe there is a tally based solution out there that works for SS2K, too.
Fortunately, I don't have to worry about it since we're through replacing our SS2K with SS2K5/2K8. It was enough of a nightmare to convert the DTS packages to SSIS and to deal with the "minor changes" like ORDER BY not working anymore in views...
Ooops! changing subject of the thread. Sorry, YSLGuru. And: Glad we could help 😀
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply