Convertir ASCII a UTF8 en SQLServer

por | Enero 2, 2012
Tenía el problema que necesitaba importar datos de un tabla con textos en ASCII a tablas del SugarCRM que estan codificadas en UTF8.
Después de mucho buscar por internet y que no me funcionara ninguna de las soluciones probadas me base en algunas de ellas e hice la mia propia.
Esta función es aumentable, ya que solo he añadido las principales letras a convertir, si tenéis más las podré ir añadiendo para toda la gente que necesite esta función.
-- =============================================
-- Author: Oscar Badiola
-- Create date: 06/08/2012 09:44:53
-- Description:	MsSQL function to convert ASCII character to UTF8
-- =============================================
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION [dbo].[fn_ASCII_2_UTF8] (@value varchar(500))
RETURNS varchar(500)
AS
BEGIN
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'Á',char(195)+char(129))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'À',char(195)+char(128))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'É',char(195)+char(137))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'È',char(195)+char(136))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'Í',char(195)+char(141))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'Ì',char(195)+char(140))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'Ó',char(195)+char(147))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'Ò',char(195)+char(146))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'Ú',char(195)+char(154))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'Ù',char(195)+char(153))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'Ï',char(195)+char(143))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'Ü',char(195)+char(156))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'Ç',char(195)+char(135))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'Ñ',char(195)+char(145))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'á',char(195)+char(161))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'à',char(195)+char(160))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'é',char(195)+char(169))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'è',char(195)+char(168))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'í',char(195)+char(173))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'ì',char(195)+char(172))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'ó',char(195)+char(179))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'ò',char(195)+char(178))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'ú',char(195)+char(186))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'ù',char(195)+char(185))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'ï',char(195)+char(175))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'ü',char(195)+char(188))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'ç',char(195)+char(167))
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,'ñ',char(195)+char(177))
	
	RETURN @value
END

Espero vuestro comentarios y se agradecen aportes.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *