Converting a value to a friendly URL format - Unicode decomposition ähhh

Question

I need to convert the value "Convert value to friendly ähhh URL format - Unicode decomposition" to "convert-value-to-url-friendly-format-unicode-decposition-ahhh". Is this possible in SQL Server? All Unicode - Symbols must be processed.

I am using SQL-Server 2005, 2008 as an option.

EDIT

Bogdan had a solution that worked for me.

The request depends on the characters you need to process, but in most cases this should be fine. You really need to go through a sort that doesn't have the characters you need to change. Cyrillic is good for this. This is a kind of hacking ...

declare @input nvarchar (4000) set @input = 'áâãäææèèêêììíníónstóôõöùúûüýÿāăąćĉċčĕėęěĝğġģĥħĩīĭįĵķļľńňŋōŏőŕřśŝşšťũūŭůűŵŷźžǻǽǿ'

SELECT CAST (@input as char (4000)) COLLATE Cyrillic_General_CI_A S

Answer 1

Here is a simple URL encoding function (it uses the varchar parameter as a parameter), which I found long ago on some forum

create function urlencode(@str as varchar(4000))
returns varchar(4000)
as
begin
declare @hex char(16)
declare @c char(1)
set @hex='0123456789ABCDEF'
declare @ostr varchar(4000)
set @ostr=''
declare @l int
set @l = 1
while @l <= len(@str)
begin
 set @c = substring(@str,@l,1)
 if @c between '0' and '9'
 or @c between 'A' and 'Z'
 or @c between 'a' and 'z'
  set @ostr = @ostr + @c
  else
  set @ostr = @ostr + '%' +
    substring(@hex,(ascii(@c)/16)+1,1)
   +substring(@hex,(ascii(@c)&15)+1,1)
 set @l=@l+1
end
return @ostr
end
go 

How will you handle unicode? Well, it's pretty simple if you don't like Hindu or Arabic characters, but care about Central European languages. You just need to use CAST (@nvarchar as varchar) .

, - .

declare @t1 nvarchar(256)
select @t1 = N'áâãäåæçèéêëìíîïðñòóôõöùúûüýÿāăąćĉċčĕėęěĝğġģĥħĩīĭįĵķļľńňŋōŏőŕřśŝşšťũūŭůűŵŷźžǻǽǿ'
select @t1
declare @t2 varchar(512)
select @t2 = cast(@t1 as varchar(512))
select @t2

,

áâãäåæçèéêëìíîïðñòóôõöùúûüýÿāăąćĉċčĕėęěĝğġģĥħĩīĭįĵķļľńňŋōŏőŕřśŝşšťũūŭůűŵŷźžǻǽǿ
aaaaa?ceeeeiiii?nooooouuuuyyaaacccceeeegggghhiiiijkllnn?ooorrsssstuuuuuwyzz???

, , . (, æ, ð, ŋ), , , ( 2 , æ = > ae).

REPLACE(), , , . , ,

1) ( ) 3 - int (0,1) , nchar (1) null, varchar (2) null 2) loop SUBSTRING() char 3) WHEN THEN

update @temp_table
set converted = CASE original 
     WHEN N'æ' THEN 'ae' 
     WHEN N'ŋ' THEN 'n'
     ... and so on ...
     ELSE CAST(original AS VARCHAR(2))

4) , , , varchar().

nvarchar() varchar() urlencode(), .

, /, , . , CAST to varchar .

CLR ( #), switch/case. , , , CLR . CLR ( SQL- CLR, , ), # ( !) SQL .

Answer 2

, . "Scalar-value User-Defined Function" (UDF).

:

• UDF T-SQL - , [I quess] " ".
• CLR UDF - , .NET.

, CLR SQL Server, ():

exec sp_configure 'clr enabled', 1
RECONFIGURE