Scripts to Fix Parts for Hidden Characters

02/12/2024 8:29 am EST

The steps are two-fold.

 1)      Create the function (below to run in the query window to create the function)

create function dbo.RemoveSpecialChars (@s varchar(1000)) returns varchar(1000)
    with schemabinding 
begin
    if @s is null
       return null
    declare @s2 varchar(1000)
    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
         if (@c between 32 and 127 or @c in (130, 144, 148, 153, 164, 165)) and --<<<<<If you need to add valid characters add the ASCII code between the ()
           @c not in (124)
          set @s2 = @s2 + char(@c)
       set @p = @p + 1
       end
    if len(@s2) = 0
       return null
    return @s2
    end

2)      Then use it on the columns that have funky characters that you want to remove (in this example, you’re doing the Detail column… there’s also Description, Part_Code, Purchase_Description, etc…)  

UPDATE IN_Part SET Detail = dbo.RemoveSpecialChars(Detail)      

UPDATE IN_Part SET Detail = dbo.RemoveSpecialChars(Detail) 
UPDATE IN_Part SET Description = dbo.RemoveSpecialChars(Description) 
UPDATE IN_Part SET Sales_Description = dbo.RemoveSpecialChars(Sales_Description) 
UPDATE IN_Product_Line SET Product_Line_Code = dbo.RemoveSpecialChars(Product_Line_Code) 
UPDATE IN_Product_Line SET Description = dbo.RemoveSpecialChars(Description)

Was this article helpful?