Voici les versions Transact-SQL des fonctions d'encodage/décodage UTF-8 que j'ai déjà posté dans ce forum pour VB :
/****************************************************************************************************
Encode une chaîne de caractères au format UTF8 / Unicode
****************************************************************************************************/
CREATE FUNCTION [dbo].[Encode_UTF8] (@ASTR nvarchar(4000))
RETURNS nvarchar(4000)
AS
BEGIN
declare @c int,@n int, @utftext nvarchar(4000)
declare @res nvarchar(4000)
set @utftext = ''
set @n = 1
WHILE (@n <= Len(@ASTR))
BEGIN
set @c = unicode(substring(@ASTR, @n, 1))
if @c < 128
begin
set @utftext = @utftext + substring(@ASTR, @n, 1)
end
else
begin
if @c >= 128 And @c < 2048
begin
set @utftext = @utftext + CHAR(((@c / 64) | 192))
set @utftext = @utftext + CHAR(((@c & 63) | 128))
end
else
begin
if @c >= 2048 And @c < 65536
begin
set @utftext = @utftext + CHAR(((@c / 4096) | 224))
set @utftext = @utftext + CHAR((((@c / 64) & 63) | 128))
set @utftext = @utftext + CHAR(((@c & 63) | 128))
end
else -- @c >= 65536
begin
set @utftext = @utftext + CHAR(((@c / 262144) | 240))
set @utftext = @utftext + CHAR((((@c / 4096) & 63) | 128))
set @utftext = @utftext + CHAR((((@c / 64) & 63) | 128))
set @utftext = @utftext + CHAR(((@c & 63) | 128))
end
end
end
set @n = @n + 1
END
return (@utftext)
END
go
/****************************************************************************************************
Décode une chaîne de caractères au format UTF8 / Unicode
****************************************************************************************************/
CREATE FUNCTION [dbo].[Decode_UTF8] (@ASTR nvarchar(4000))
RETURNS nvarchar(4000)
AS
BEGIN
Declare @c0 int, @c1 int, @c2 int, @c3 int, @n int ,@unitext nvarchar(4000)
set @unitext = ''
set @n = 1
if dbo.is_Utf8(@astr) = 0
begin
RETURN(@ASTR)
end
WHILE @n <= len(@ASTR)
BEGIN
set @c0 = ascii(substring(@astr, @n, 1))
If @n <= Len(@astr) - 1
begin
set @c1 = ascii(substring(@astr, @n + 1, 1))
end
Else
begin
set @c1 = 0
End
If @n <= Len(@astr) - 2
begin
set @c2 = ascii(substring(@astr, @n + 2, 1))
end
Else
begin
set @c2 = 0
End
If @n <= Len(@astr) - 3
begin
set @c3 = ascii(substring(@astr, @n + 3, 1))
End
Else
begin
set @c3 = 0
End
If (@c0 & 240) = 240 And (@c1 & 128) = 128 And (@c2 & 128) = 128 And (@c3 & 128) = 128
begin
set @unitext = @unitext + nchar((@c0 - 240) * 65536 + (@c1 - 128) * 4096) + (@c2 - 128) * 64 + (@c3 - 128)
set @n = @n + 4
end
Else
begin
If (@c0 & 224) = 224 And (@c1 & 128) = 128 And (@c2 & 128) = 128
begin
set @unitext = @unitext + nchar((@c0 - 224) * 4096 + (@c1 - 128) * 64 + (@c2 - 128))
set @n = @n + 3
end
else
begin
If (@c0 & 192) = 192 And (@c1 & 128) = 128
begin
set @unitext = @unitext + nchar((@c0 - 192) * 64 + (@c1 - 128))
set @n = @n + 2
end
Else
begin
If (@c0 & 128) = 128
begin
set @unitext = @unitext + nchar(@c0 & 127)
set @n = @n + 1
end
Else -- c0 < 128
begin
set @unitext = @unitext + nchar(@c0)
set @n = @n + 1
end
end
end
End
END
RETURN(@unitext)
END
go
/****************************************************************************************************
Vérifie qu'une chaîne de caractères est au format UTF8 / Unicode
****************************************************************************************************/
CREATE FUNCTION [dbo].[Is_UTF8](@ASTR nvarchar(4000))
RETURNS tinyint
AS
BEGIN
declare @c0 int, @c1 int, @c2 int, @c3 int, @n int
set @n = 1
while @n <= Len(@ASTR)
begin
set @c0 = ascii(substring(@ASTR, @n, 1))
if (@n <= Len(@ASTR) - 1)
set @c1 = ascii(substring(@ASTR, @n + 1, 1))
else
set @c1 = 0
if (@n <= Len(@ASTR) - 2 )
set @c2 = ascii(substring(@ASTR, @n + 2, 1))
else
set @c2 = 0
if (@n <= Len(@ASTR) - 3 )
set @c3 = ascii(substring(@ASTR, @n + 3, 1))
else
set @c3 = 0
If (@c0 & 240) = 240
begin
If (@c1 & 128) = 128 And (@c2 & 128) = 128 And (@c3 & 128) = 128
set @n = @n + 4
Else
return 0
end
Else
begin
If (@c0 & 224) = 224
begin
If (@c1 & 128) = 128 And (@c2 & 128) = 128
set @n = @n + 3
Else
return 0
end
Else
begin
If (@c0 & 192) = 192
begin
If (@c1 & 128) = 128
set @n = @n + 2
Else
return 0
end
Else
begin
If (@c0 & 128) = 0
set @n = @n + 1
Else
return 0
end
end
end
end
return 1
END
go
Message édité par Cyberpat92 le 12-02-2007 à 13:54:59