title: Parse URL in SQL Server table to extract Key and Value Pairs
body: <p>I have a table with a URL field.  It contains random urls from customer sites.  I want to extract the key and value from those urls that have a querystring.  For example if I have:</p>
  
  <blockquote>
    <p><a href="http://www.tigerdirect.ca/applications/SearchTools/item-details.asp?EdpNo=2580461&amp;sku=B69-1412&amp;SRCCODE=CANEMBP004&amp;cm_mmc=Email-_-Retro-_-CANEMBP004-_-viewed" rel="nofollow">http://www.tigerdirect.ca/applications/SearchTools/item-details.asp?EdpNo=2580461&amp;sku=B69-1412&amp;SRCCODE=CANEMBP004&amp;cm_mmc=Email-<em>-Retro-</em>-CANEMBP004-_-viewed</a></p>
  </blockquote>
  
  <p>I need the stored procedure to return a Key and Value in the result.  </p>
  
  <pre><code>EdpNo=2580461
  sku=B69-1412
  SRCCODE=CANEMBP004
  cm_mmc=Email-_-Retro-_-CANEMBP004-_-viewed
  </code></pre>
  
  <p>Where <code>EdpN</code>o is the Key and <code>2580461</code> is the value in the first example</p>
  
  <p>I have looked at a few user defined functions but they all work on passing in just one url at a time where I need to scan the url field in a table.  </p>
  
  <p>Perhaps I just don't know how to use them, so any insight on what I have or perhaps how it can be restructured would be much appreciated.</p>
  
  <p>UDF:</p>
  
  <pre><code>ALTER FUNCTION [dbo].[SplitQueryString] (@s varchar(8000))
  RETURNS table
  AS
  RETURN (
      WITH splitter_cte AS (
        SELECT CHARINDEX('&amp;', @s) as pos, 0 as lastPos
        UNION ALL
        SELECT CHARINDEX('&amp;', @s, pos + 1), pos
        FROM splitter_cte
        WHERE pos &gt; 0
        ),
      pair_cte AS (
      SELECT chunk,
             CHARINDEX('=', chunk) as pos
      FROM (
          SELECT SUBSTRING(@s, lastPos + 1,
                           case when pos = 0 then 80000
                           else pos - lastPos -1 end) as chunk
          FROM splitter_cte) as t1
    )
      SELECT substring(chunk, 0, pos) as keyName,
             substring(chunk, pos+1, 8000) as keyValue
      FROM pair_cte
  );
  </code></pre>