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.

[code lang=”sql”]
— =============================================
— 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(128))
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(135))
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(137))
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(141))
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(145))
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(147))
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(156))
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(161))
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(168))
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(170))
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(173))
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(177))
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(179))
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(186))
SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,’ü’,char(195)+char(188))

RETURN @value
END
[/code]

Espero vuestro comentarios y se agradecen aportes.

Actualizaciones
02/11/2017
Gracias Jesus por tu aporte.

2 pensamientos en “Convertir ASCII a UTF8 en SQLServer

  1. Jesus

    Gracias Oscar.

    Yo he utilizado la función en orden inverso. Es decir:

    SELECT @value = REPLACE(@value COLLATE Latin1_General_CS_AS,char(194)+char(170),’ª’)

    Por cierto he añadido el carácter ‘ª’ que también me daba problemas.

    Responder

Deja un comentario

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