Tsql - How to Url Encode

urlencode with only built-in functions

select regexp_replace(encode('héllo there','hex'),'(..)',E'%\\1','g');

This doesn't leave the alphanumeric characters human-readable, though.

SQL Server Url Decoding

Try one of these:

CREATE FUNCTION dbo.UrlDecode(@url varchar(3072))
RETURNS varchar(3072)
AS
BEGIN 
    DECLARE @count int, @c char(1), @cenc char(2), @i int, @urlReturn varchar(3072) 
    SET @count = Len(@url) 
    SET @i = 1 
    SET @urlReturn = '' 
    WHILE (@i <= @count) 
     BEGIN 
        SET @c = substring(@url, @i, 1) 
        IF @c LIKE '[!%]' ESCAPE '!' 
         BEGIN 
            SET @cenc = substring(@url, @i + 1, 2) 
            SET @c = CHAR(CASE WHEN SUBSTRING(@cenc, 1, 1) LIKE '[0-9]' 
                                THEN CAST(SUBSTRING(@cenc, 1, 1) as int) 
                                ELSE CAST(ASCII(UPPER(SUBSTRING(@cenc, 1, 1)))-55 as int) 
                            END * 16 + 
                            CASE WHEN SUBSTRING(@cenc, 2, 1) LIKE '[0-9]' 
                                THEN CAST(SUBSTRING(@cenc, 2, 1) as int) 
                                ELSE CAST(ASCII(UPPER(SUBSTRING(@cenc, 2, 1)))-55 as int) 
                            END) 
            SET @urlReturn = @urlReturn + @c 
            SET @i = @i + 2 
         END 
        ELSE 
         BEGIN 
            SET @urlReturn = @urlReturn + @c 
         END 
        SET @i = @i +1 
     END 
    RETURN @urlReturn
END
GO

from http://sqlblog.com/blogs/peter_debetta/archive/2007/03/09/t-sql-urldecode.aspx

---

CREATE FUNCTION dbo.fnDeURL
(
    @URL VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @Position INT,
        @Base CHAR(16),
        @High TINYINT,
        @Low TINYINT,
        @Pattern CHAR(21)

    SELECT  @Base = '0123456789abcdef',
        @Pattern = '%[%][0-9a-f][0-9a-f]%',
        @URL = REPLACE(@URL, '+', ' '),
        @Position = PATINDEX(@Pattern, @URL)

    WHILE @Position > 0
        SELECT  @High = CHARINDEX(SUBSTRING(@URL, @Position + 1, 1), @Base COLLATE Latin1_General_CI_AS),
            @Low = CHARINDEX(SUBSTRING(@URL, @Position + 2, 1), @Base COLLATE Latin1_General_CI_AS),
            @URL = STUFF(@URL, @Position, 3, CHAR(16 * @High + @Low - 17)),
            @Position = PATINDEX(@Pattern, @URL)

    RETURN  @URL
END

from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88926

Server.UrlEncode for SQL?

You are looking to perform Percent-Encoding/Decoding which is defined in Section 2.1 of RFC 3986, titled "Uniform Resource Identifier (URI): Generic Syntax".

You can use this section to determine how to percent-encode/decode URIs in T-SQL.

Specifically, you'll want to pay attention to all of Section 2: Characters with specific attention paid to Section 2.4: When to Encode or Decode.

The best way to do this, of course, is to perform this before/after you save/query the database. Relational databases in general aren't made for this kind of computational work.

If you use code, you might want to take a look at the Microsoft Web Protection Library on CodePlex, as it contains mechanisms for percent-encoding/decoding which are much more up-to-date in regards to taking into account vulnerabilities that can arise from poor encoding schemes.

Does Pervasive Have a SQL Function for URL Encoding?

Based on the answer give here, you can create a function using:

CREATE FUNCTION urlencode(:description char(200))
RETURNS char(200)
AS
BEGIN
  SELECT 
   Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
   Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
   Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
   Replace(RTRIM(:description)
       ,'%','%25')       ,'&','%26')       ,'$','%24')       ,'+','%2B')
       ,',','%2C')       ,':','%3A')       ,';','%3B')       ,'=','%3C')
       ,'?','%3D')       ,':','%3F')       ,'@','%40')       ,'#','%23')
       ,'<','%3C')       ,'>','%3E')       ,'[','%5B')       ,']','%5D')
       ,'{','%7B')       ,'}','%7D')       ,'|','%7C')       ,'^','%5E')
       ,' ','%20')       ,'~','%7E')       ,'`','%60')       ,'*','%2A')
       ,'(','%28')       ,')','%29')       ,'/','%2F')       ,'\\','%5C')
       ,' ','%20') INTO :description;
  RETURN :description;
END;

Url Decode T-SQL function not translating out of ascii scope characters

I found this function that achieve exactly what I want :

ALTER FUNCTION [dbo].[UrlDecodeUTF8](@URL varchar(3072))
RETURNS varchar(3072)
AS
BEGIN 
    DECLARE @Position INT,
        @Base CHAR(16),
        @Code INT,
        @Pattern CHAR(21)

    SELECT @URL = REPLACE(@URL, '%c3', '')

    SELECT  @Base = '0123456789abcdef',
        @Pattern = '%[%][0-9a-f][0-9a-f]%',
        @Position = PATINDEX(@Pattern, @URL)

    WHILE @Position > 0
        SELECT @Code = Cast(CONVERT(varbinary(4), '0x' + SUBSTRING(@URL, @Position + 1, 2), 1) As int),
            @URL = STUFF(@URL, @Position, 3, NCHAR(@Code + 64)),
            @Position = PATINDEX(@Pattern, @URL)

    RETURN REPLACE(@URL, '+', ' ')

END

MS SQL URL Encoded XML Reading Child Node

Well, you seem to have two problems here:

1. Embedded XML, and
2. Embedded XML with namespaces.

The elements you're trying to query in the embedded have a default namespace defined on the QueryBuilder element of http://dev.docuware.com/settings/web/querybuilder, so you'll need to reference that in your XPath query...

with xmlnamespaces (
  'http://dev.docuware.com/settings/web/querybuilder' as qb
)
select Provider.value('(qb:Value/text())[1]', 'nvarchar(max)') as Value
from (
  select cast(ExtendedStatement.value('(./text())[1]', 'nvarchar(max)') as xml) as EmbeddedXML
  from dbo.DWFCProfile
  cross apply settings.nodes('/FCProfile/IndexFilters/SearchFilter/ExtendedStatement') SearchFilter(ExtendedStatement)
  where fid = 5
) Embedded
cross apply EmbeddedXML.nodes('/qb:QueryBuilderSettings/qb:Root/qb:Conditions/qb:Provider') as Conditions(Provider);

Because there are so many left joins involved in your extended query you can use outer apply instead of cross apply to allow rows to be returned even if some values might be null. So that looks like the following (untested, because I don't have your database available)...

with xmlnamespaces (
  'http://dev.docuware.com/settings/web/querybuilder' as qb
)
select
  a.name as Benutzer,
  c.name as Gruppe,
  e.name as Rolle,
  i.name as Archivprofile,
  k.name as Archiv,
  Provider.value('(qb:Value/text())[1]', 'nvarchar(max)') as Value
from dbo.DWUser as a
left join dbo.DWUserToGroup b on b.uid=a.uid
left join dbo.DWGroup c on c.gid=b.gid
left join dbo.DWUserToRole d on d.uid=a.uid
left join dbo.DWGroupToRole j on j.gid=c.gid
left join dbo.DWRoles e on e.rid=j.rid
left join dbo.DWFCProfileToRole h on h.rid=e.rid
left join dbo.DWFCProfile i on i.fpid=h.fpid
left join dbo.DWFileCabinet k on k.fid=i.fid
outer apply i.settings.nodes('/FCProfile/IndexFilters/SearchFilter/ExtendedStatement') SearchFilter(ExtendedStatement)
outer apply (
  select cast(ExtendedStatement.value('(./text())[1]', 'nvarchar(max)') as xml) as EmbeddedXML
) Embedded
outer apply EmbeddedXML.nodes('/qb:QueryBuilderSettings/qb:Root/qb:Conditions/qb:Provider') as Conditions(Provider)
order by k.name;

---

Related Topics