Convertir UTF8 a ASCII en SQLServer

por | enero 1, 2012
Tenía el programa que necesitaba sacar informes de los datos del SugarCRM, esos datos estaban codificados en UTF8 y al exportar los resultados de las select me aparecian simbolos extraños.
He creado una función para poder exportar los datos de la selects para que seal leibles.
Esta función es aumentable, ya que solo he añadido las principales letras a convertir, si tenéis mas las podre 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 UTF8 character to ASCII
-- =============================================
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION [dbo].[fn_UTF8_2_ASCII] (@value varchar(250))
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),'ñ')
	
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,char(194)+char(186),'º')
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,char(194)+char(170),'ª')
	SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,char(226)+char(130)+char(172),'€')
 
	RETURN @value
END

Espero vuestro comentarios y se agradecen aportes.
Categoría: Sin categoría

Deja un comentario

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