hsuk hsuk - 7 months ago 27
SQL Question

Get and remove query string with SQL

I'm looking for a SQL function that can get query string in the sense, URL is stored on a variable.

Actually, I need to create a trigger that will update the column containing url and encode the particular query string of the url, so to encode that particular query string, first I need to extract the query string value.

Lets say

@url = 'mypage.php?name=This is test&address=Test Address&phone=+9779854125896'


Then, I need a SQL function like

select getURLparams('name', @url)


that should return

This is test


And, how to delete particular query string, so I could append the updated one.

I am on SQL Sever 2008 R2.

Answer

Please try below query which returns querystring value with querystring name as parameter:

declare @url nvarchar(max) = 'mypage.php?name=This is test&address=Test Address&phone=+9779854125896'

declare @param nvarchar(max)='phone'

SELECT 
    (CASE WHEN CHARINDEX('&', v)>0 THEN SUBSTRING(v, 0, CHARINDEX('&', v))
    ELSE V END)
FROM(
    SELECT SUBSTRING(@url, CHARINDEX(@param+'=', @url)+LEN(@param)+1, len(@url)) v
    WHERE CHARINDEX('&'+@param+'=', REPLACE(@url, '?', '&'))>0
)x

For changing the value of an existing querystring, please try:

declare @url nvarchar(max) = 'mypage.php?name=This is test&address=Test Address&phone=+9779854125896'

declare @param nvarchar(max)='name'
declare @NewValu nvarchar(max)='Test'


SELECT 
    REPLACE(@url, Ch+vv, Ch+@NewValu)
FROM(
    SELECT  
        (CASE WHEN CHARINDEX('&', v)>0 then SUBSTRING(v, 0, CHARINDEX('&', v))
        ELSE V END) vv,
        (CASE WHEN CHARINDEX('?'+@param+'=', @url)>0 
                    THEN '?'+@param+'='
                    ELSE '&'+@param+'=' END) Ch
    FROM(
        SELECT SUBSTRING(@url, CHARINDEX(@param+'=', @url)+LEN(@param)+1, len(@url)) v
        WHERE CHARINDEX('&'+@param+'=', REPLACE(@url, '?', '&'))>0
    )x
)xx
Comments