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.

Community · Accepted Answer · 2017-05-23 11:56:49Z

create function dbo.fn_splitQuerystring(@querystring nvarchar(4000))
returns table 
as
/*
 * Splits a querystring-formatted string into a table of name-value pairs
 * Example Usage:
        select * from dbo.fn_splitQueryString('foo=bar&baz=qux&x=y&y&abc=')
 */
return ( 
    select  'name' = SUBSTRING(s,1,case when charindex('=',s)=0 then LEN(s) else charindex('=',s)-1 end) 
        ,   'value' = case when charindex('=',s)=0 then '' else SUBSTRING(s,charindex('=',s)+1,4000) end    
    from dbo.fn_split('&',@querystring)
)
go

Which utilises this general-purpose split function:

create function dbo.fn_split(@sep nchar(1), @s nvarchar(4000))
returns table
/*
 * From https://stackoverflow.com/questions/314824/
 * Splits a string into a table of values, with single-char delimiter.
 * Example Usage:
        select * from dbo.fn_split(',', '1,2,5,2,,dggsfdsg,456,df,1,2,5,2,,dggsfdsg,456,df,1,2,5,2,,')
 */
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS s
    FROM Pieces
  )
go

Ultimately letting you do something like this:

select name, value
from dbo.fn_splitQuerystring('foo=bar&baz=something&x=y&y&abc=&=whatever')

Community · Accepted Answer · 2017-05-23 12:19:32Z

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.

edited May 23, 2017 at 12:19

CommunityBot

11 silver badge

answered Apr 16, 2009 at 12:10

Ed Guiness

35.5k16 gold badges114 silver badges149 bronze badges

1 Comment

Rory Over a year ago

There are reasons that in my case I need to do this from the database layer, unfortunately as obviously it would be a lot more straightforward to do using .NET code as per those links.

j_random_hacker · Accepted Answer · 2009-04-16 12:11:38Z

0

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.

answered Apr 16, 2009 at 12:11

j_random_hacker

51.6k10 gold badges109 silver badges176 bronze badges

5 Comments

Rory Over a year ago

thanks, but i meant 'querystring' as in the part of the url after the ?. I don't mean that parts of a query will be in the url.

j_random_hacker Over a year ago

@Rory: Yes, that's what I mean too.

Rory Over a year ago

ok, in that case your response isn't an answer to my question: I have data in my database and I want to turn it into a table of name/value pairs. I'm not putting queries into the query string.

j_random_hacker Over a year ago

@Rory: Ah, I misunderstood sorry (three times actually...) So you mean you have a URL like "mysite.com/xyz?foo=bar;baz=qux;x=y" and you want to produce a 2-column (key and val) table (with 3 rows in this example)? I'm sure it can be done, but I'm not sure how unfortunately.

Rory Over a year ago

yes exactly. no worries - i guess it shows i should write my questions clearer. thanks anyway.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related