Please support Unicode in automatic links

Question

I want to create a SEDE query where one of the columns uses manual autolinking, i.e. the values are a strings of the form http://example.com/blah|link text or site://blah|link text. This doesn't work when the URL contains non-ASCII characters: instead of getting link text, I just get the literal text http://example.com/blah|link text.

A workaround could be to URL-encode the URL, but I don't know how to do that.

Specifically, I want to list tag names containing diacritics. The lack of Unicode support is pretty annoying here. A future revision will support autolinking of non-ASCII tag names (thanks hichris123) — please support Unicode in all links.

Answer 1

Until this feature request is implemented a workaround is possible by using the workaround for using functions:

I took your original query, forked it and implemented an answer from How to URL Encode (you edited that answer) which gives you this query:

CREATE PROCEDURE #urlencode(
     @url NVarChar(25)
   , @ret NVarChar(1024) OUTPUT -- This is key!
)
AS BEGIN
    /* https://stackoverflow.com/a/2905728/578411 */
 DECLARE @count INT, @c NCHAR(1), @i INT, @urlReturn NVARCHAR(3072)
    SET @count = LEN(@url)
    SET @i = 1
    SET @urlReturn = ''    
    WHILE (@i <= @count)
     BEGIN
        
        SET @c = SUBSTRING(@url, @i, 1)
        IF @c LIKE N'[A-Za-z0-9()''*\-._!~]' COLLATE Latin1_General_BIN ESCAPE N'\' COLLATE Latin1_General_BIN
         BEGIN
            print 'noenc: ' + @c
            SET @urlReturn = @urlReturn + @c
         END
        ELSE
         BEGIN
            print 'enc: ' + @c
            SET @urlReturn = 
                   @urlReturn + '%'
                   + SUBSTRING(sys.fn_varbintohexstr(CAST(@c AS VARBINARY(MAX))),3,2)
                   + ISNULL(NULLIF(SUBSTRING(sys.fn_varbintohexstr(CAST(@c AS VARBINARY(MAX))),5,2), '00'), '')
         END
        SET @i = @i +1
     END
     print @urlReturn
    SELECT @ret = @urlReturn

END
GO

declare cur_tags cursor for 
           SELECT t.id
           , tagname
           FROM Tags t
           WHERE t.TagName LIKE '%[^-#+.0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]%'

declare @tagname nvarchar(25)
declare @tagid int
declare @tagname_enc nvarchar(25)
create table #tags(
      id int
   ,  tagname nvarchar(25) COLLATE SQL_Latin1_General_CP1_CS_AS ) -- weird defaults

open cur_tags;

FETCH NEXT FROM cur_tags 
INTO @tagid, @tagname
-- print ' open ' + @tag
WHILE @@FETCH_STATUS = 0
BEGIN
      
   EXECUTE #urlencode  @tagname, @tagname_enc OUT
   print @tagname +':'+  @tagname_enc
   INSERT INTO #tags values (@tagid, @tagname_enc)

   FETCH NEXT FROM cur_tags
   INTO @tagid, @tagname   
END

close cur_tags;

select t.id
  , t.TagName AS [Tag]
  , 'site://tags/' + t.tagname + '|' + rt.tagname as [taglink]
  , rt.Count AS [Count]
  , (
      SELECT s.SourceTagName + ' '
      FROM TagSynonyms s
      WHERE s.ApprovedByUserId IS NOT NULL AND s.TargetTagName = rt.TagName
      FOR XML PATH('')
    ) AS [Synonyms]
from #tags t
inner join tags rt on rt.id = t.id -- rt is the original tags table
ORDER BY t.TagName ASC

The results gives you working urlencoded links for tagnames with diacritics.