In SQL replace unicode characters with latin characters

PetEdge used Ask the Experts™
I import a UTF16 customer file from SAP BW, on a daily basis, into a staging table (SQL 2008R2).

We have a requirement to extract data without unicode characters.

I would like to replace unicode characters with the Latin equivalent.

For example:
à  ->  a
ś   ->  s
ï   ->   i
æ   ->  ae

Some data may be malformed and I'd also like to clean it up:

Sa³ley Ma¶ller  ->  Soley Moller
Trois-RiviÃ?¨res  -> Trois-Rivieres

Here is current collation:
SELECT SERVERPROPERTY('Collation')  --SQL_Latin1_General_CP1_CI_AS

Thank you for looking at this.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

I think you need to replace them manually

Update table  set Yourcolumn = replace( replace (YourColumn, 'à', 'a') , 'ś', 's')
you may like to try this function

-- Removes special characters from a string value.
-- All characters except 0-9, a-z and A-Z are removed and
-- the remaining characters are returned.
-- Author: Christian d'Heureuse,
create function dbo.RemoveSpecialChars (@s varchar(256)) returns varchar(256)
   with schemabinding
   if @s is null
      return null
   declare @s2 varchar(256)
   set @s2 = ''
   declare @l int
   set @l = len(@s)
   declare @p int
   set @p = 1
   while @p <= @l begin
      declare @c int
      set @c = ascii(substring(@s, @p, 1))
      if @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122
         set @s2 = @s2 + char(@c)
      set @p = @p + 1
   if len(@s2) = 0
      return null
   return @s2

Open in new window

-- how to use

CREATE TABLE [dbo].[t1](
	[NAME] [nvarchar](max) NULL


insert into t1 (NAME)
Select 'Trois-Rivi�¨res'
select  dbo.RemoveSpecialChars (NAME) from t1 GO

Open in new window

--result TroisRivires


Thank you very much!  I customized it a little and created a look up table and it's working very well.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial