[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 921
  • Last Modified:

In SQL replace unicode characters with latin characters

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.
1 Solution
Aneesh RetnakaranDatabase AdministratorCommented:
I think you need to replace them manually

Update table  set Yourcolumn = replace( replace (YourColumn, 'à', 'a') , 'ś', 's')
Eugene ZCommented:
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, www.source-code.biz
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
PetEdgeAuthor Commented:
Thank you very much!  I customized it a little and created a look up table and it's working very well.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now