In SQL replace unicode characters with latin characters

Posted on 2014-08-12
Last Modified: 2014-08-16
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.
Question by:PetEdge
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    I think you need to replace them manually

    Update table  set Yourcolumn = replace( replace (YourColumn, 'à', 'a') , 'ś', 's')
    LVL 42

    Accepted Solution

    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
    ) ON [PRIMARY]
    insert into t1 (NAME)
    Select 'Trois-Rivi�¨res'
    select  dbo.RemoveSpecialChars (NAME) from t1 GO

    Open in new window

    --result TroisRivires

    Author Closing Comment

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

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now