Parsing a Querystring using TSQL.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Parsing a Querystring using TSQL.

Author

Topic

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-03-11 : 12:53:08

I need each param parsed out of this string. I am having trouble parsing if the vars vary in length.

Data: ec=TTT&oc=CRM&tot=501&UID=88997


(SELECT
	   ID, IP, TID, TheUserDetails.UserID as UserID, 
	   --Parsing
	   Querystring,
	   SUBSTRING(Querystring, CHARINDEX('ec=',Querystring) + 3, CHARINDEX('&', Querystring) - 4) as EC,
	   SUBSTRING(Querystring, CHARINDEX('oc=',Querystring) + 3, CHARINDEX('&', Querystring) - 4) as OC,
	   SUBSTRING(Querystring, CHARINDEX('tot=',Querystring) + 4, CHARINDEX('&', Querystring) - 5) as TOT,
	   SUBSTRING(Querystring, CHARINDEX('uid=',Querystring) + 4, 50) as UID
	  FROM WebLog l LEFT JOIN
                     dbo.TheUserDetails ON 
		     l.TID = TheUserDetails.TrackID)

Edited by - jesus4u on 03/11/2003 12:53:33

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-11 : 13:04:19

As you had it it would always get the first &

Something like
SUBSTRING(Querystring, CHARINDEX('oc=',Querystring) + 3, CHARINDEX('&', Querystring, CHARINDEX('oc=',Querystring)) - 4) as OC

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-03-11 : 13:11:11

thanks but yours didn't quite do it...

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-03-12 : 04:25:33

Try something like this. I would implement it as a function or procedure, if you need to reuse the code. It will parse any querystring, and put it in the table variable @p, regardless of the number of variables.

set nocount on
declare @qs varchar(50),
@foo varchar(50)
select @qs='ec=TT&oc=CRM&totttt=501erer&UID=88997'

declare @p table
(
varname varchar(20),
varvalue varchar(20)
)

select @foo=substring(@qs, 1, charindex('&', @qs)-1)

while @foo > '' and @qs > ''
begin
insert @p
select substring(@foo, 1, charindex('=', @qs)-1),
substring(@foo, charindex('=', @qs)+1, 50)

if charindex('&', @qs)>0
select @qs=replace(@qs, @foo+'&', '')
else
select @qs=replace(@qs, @foo, '')
if charindex('&', @qs)>0
select @foo=substring(@qs, 1, charindex('&', @qs)-1)
else
select @foo=@qs

end
select * from @p

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-12 : 06:47:07

Why would you ever need to parse a querystring in sql?

Jay White
{0}

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-03-12 : 08:08:59

quote:

Why would you ever need to parse a querystring in sql?

Jay White
{0}

Because we do custom IIS logfile analysis.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources