Translating ANSI Column into Unicode (Arabic Code Page)

Introduction

This code addresses an issue faced by some when the source database column is based on a collation that is not the same as the destination - in our example, the target is SQL_Latin1_General_CP1256_CI_AS (whether it's another table or just the selection statement).

Background

You're migrating data from Oracle database table where collation of one column (that is of varchar or char ANSI type default code page) is SQL_Latin1_General_CP1_CI_AS to Microsoft SQL server table where the collation of the target column (that is of nchar or nvarchar unicode type - Arabic code page) is SQL_Latin1_General_CP1256_CI_AS. If you do a simple collation casting, the data will be lost. This script preserves the data in case it's for Arabic alphabets. A mapping table is used in this translation. 

Using the Code 

To use the code: 

1. In the target server, deploy the function listed below (in a database with a compatible/matching collation with the source).
2. Use the function by calling it on the column which requires translation.

Example:

select TRAN_DATE,TRAN_NO1,TR_DESC,DEBIT,CREDIT  from openquery(ALPHA,'
select TRAN_DATE,,^__strong style="FONT-STYLE: normal">master.dbo.to_unicode(TR_DESC) _
TR_DESC,DEBIT,CREDIT from OPENQUERY(alpha_oracle,
''
SELECT TRAN_DATE,TRAN_NO1,TR_DESC,DEBIT,CREDIT FROM SUP_TRAN
where 1=1
and TRAN_DATE BETWEEN {ts ''''2000-01-01 00:00:00.000''''} AND {ts ''''2010-12-31 00:00:00.000''''}
and TRAN_YEAR between 2000 and 2010
and SUP_NO=''''1014985'''''')') 

 The body of the function follows: 

USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[to_unicode]    Script Date: 05/07/2012 14:45:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[to_unicode](@mystr nvarchar(4000))
returns nvarchar(4000) as
begin
declare @output nvarchar(4000)
declare @counter int
declare @tchar nchar(3)
declare @lookup nchar(61)
declare @patterns nchar(245)
declare @instr int

set @lookup=N'٠١٢٣٤٥٦٧٨٩‘،؛؟ءآأؤإئابةتثجحخدذرزسشصض×طظعغـفقكلمنهوىيًٌٍَُِ÷ّْ'
set @patterns=_
N'.48 .49 .50 .51 .52 .53 .54 .55 .56 .57 .145.161.186.191.193.194.195.196.197.198.199._
200.201.202.203.204.205.206.207.208.209.210.211.212.213.214.215.216.217.218.219.220.221._
222.223.225.227.228.229.230.236.237.240.241.242.243.245.246.247.248.250.'

set @output=N''
set @counter = 1
while @counter <= len(@mystr)
begin
set @tchar = convert(nchar(3),ascii(substring(@mystr,@counter,1)))

set @instr=patindex(N'%.'+@tchar+N'.%',@patterns)
if @instr>0
begin
set @instr=((@instr-1)/4)+1
set @output=@output+substring(@lookup,@instr,1)
end
else
set @output=@output+nchar(@tchar)

set @counter=@counter+1
end

return @output
end

Points of Interest 

This is the second time I am writing this code; the first time was back in 2005.

I decided to share it online since I couldn't find a useful link to any similar code and I just didn't want to write it again later. Keeping it online will probably preserve it better for future use.

History

• 05-07-2012 First published official public release