Click here to Skip to main content
Click here to Skip to main content

Translating ANSI Column into Unicode (Arabic Code Page)

, 7 Mar 2013 Public Domain
Rate this:
Please Sign up or sign in to vote.
Mapping table is used in the translation - output in Unicode (nvarchar)

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,master.dbo.to_unicode(TRAN_NO1) TRAN_NO1,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

 

License

This article, along with any associated source code and files, is licensed under A Public Domain dedication

Share

About the Author

Emad Sammour
Software Developer (Senior)
Palestinian Territory (Occupied) Palestinian Territory (Occupied)
No Biography provided
Follow on   Twitter   Google+

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150327.1 | Last Updated 7 Mar 2013
Article Copyright 2012 by Emad Sammour
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid