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.
TagName
and notTag Link
. Thanks.