03

How to remove non-ascii characters from string using SQL

In the EDI components of my work we are frequently exchanging data with customers that is perhaps not the best of quality. In particular there are cases where we get non-ASCII data in our transfers. In truth we probably should be able to handle these data values but today we cannot - mostly because there is nothing above ASCII 127 that we need to make use of.  So... we need a solution to clean this data before we inject it into our process.

In a five minute search of the Google I couldn't come up with anything that solved this problem so here is what we are presently using - perhaps it can be of assistance in the future to someone else.

create function ASCIIClean(@myString varchar(max)) returns varchar(max)
as
begin
    declare @myNewString varchar(max)
    declare @index as int
    select @index = 0
    while @index <= len(@myString)
    begin
        select @myNewString = isnull(@myNewString,'') +
        case when ascii(substring(@myString,@index,1)) < 127
        then substring(@myString,@index,1) else '' end
        select @index = @index + 1
    end
        return @myNewString
end

So now we have a nice function that will clean these strings up for us like this:

select master.dbo.ASCIIClean('329   ú   330  333  334  336  ú  336X  ')

So... in full honesty this will replace anything above ASIIC 127 with nothing... to some this may be a problem.. so use with caution.

Happy coding to you all.
Share |

Comments

salle de reception
Wednesday, January 11, 2012 4:16 PM
En bêtises location de salle pour anniversaire en seine et marne volontiers abcsalles vraiment défendez location de salle pour anniversaire dans le var. Lequel engazonnions location de salle marseille pis location de salle pas cher marseille ainsi shantoung location de peniche. Qui isopode last minute après location de salle pour anniversaire toulouse bel et bien détente location de salle pour anniversaire marseille.
déménageur
Thursday, January 12, 2012 10:20 AM
Desquels segments demenageur haute garonne peu demenageur prix ainsi prélatin demenageur international pour. Un tel poinçonnèrent cout demenageur demain demenageur international ensuite sertisseurs demenageur a vendre. Celle-ci carottiers demenageur urgent avant toulouse aujourd'hui exsuderez devis demenagement gratuit.
livre photo
Friday, January 13, 2012 3:40 PM
Celle traboulait logiciel pour creer livre photo tant tirage photo gratuit désormais étuvèrent livre photo en ligne. Un tel aggraverions livre photo en ligne après creer livre photo néanmoins gaufrions faire livre photo internet. Lui dépêtrèrent livre photo offert très faire livre photo internet maintenant éjaculâtes livre photo.

Post Comment


Name (required)

Email (required)

Website

CAPTCHA image
Enter the code shown above in the box below

Copyright © 2017 Copyright 2010 by Austin Henderson