sql function to return table of names and values given a querystring

Question

Anyone have a t-sql function that takes a querystring from a url and returns a table of name/value pairs?

eg I have a value like this stored in my database:

foo=bar;baz=qux;x=y

and I want to produce a 2-column (key and val) table (with 3 rows in this example), like this:

name  | value
-------------
foo   | bar
baz   | qux
x     | y

UPDATE: there's a reason I need this in a t-sql function; I can't do it in application code. Perhaps I could use CLR code in the function, but I'd prefer not to.

UPDATE: by 'querystring' I mean the part of the url after the '?'. I don't mean that part of a query will be in the url; the querystring is just used as data.

Answer 1

I'm sure TSQL could be coerced to jump through this hoop for you, but why not parse the querystring in your application code where it most probably belongs?

Then you can look at this answer for what others have done to parse querystrings into name/value pairs.

Or this answer.

Or this.

Or this.

Answer 2

Please don't encode your query strings directly in URLs, for security reasons: anyone can easily substitute any old query to gain access to information they shouldn't have -- or worse, "DROP DATABASE;". Checking for suspicious "keywords" or things like quote characters is not a solution -- creative hackers will work around these measures, and you'll annoy everyone whose last name is "O'Reilly."

Exceptions: in-house-only servers or public https URLS. But even then, there's no reason why you can't build the SQL query on the client side and submit it from there.