How to Remove Special Characters from Text String in MS SQL (T-SQL)

Was looking for a better solution than a mile long REPLACE statement, found this. Originally written by Christian d’Heureuse, modified slightly by me. Removes all characters except: 0-9, a-z, A-Z, and spaces (remove “or @c = 32” if you want to also remove spaces).

/****** Object:  UserDefinedFunction [dbo].[RemoveSpecialChars]    Script Date: 09/29/2009 09:48:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[RemoveSpecialChars] (@s varchar(256)) returns varchar(256)
   with schemabinding
begin
   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 or @c = 32
         set @s2 = @s2 + char(@c)
      set @p = @p + 1
      end
   if len(@s2) = 0
      return null
   return @s2
   end

Unable to Import Text File to SQL, “Truncated Data” Error

There seems to be a bug or at least an oversight in all versions of MS SQL wherein you cannot import from a text file to a table if SQL doesn’t like the data in the source file. I’ve tried everything from saving the text to an Excel sheet in a trimmed column to selecting only certain columns for import, nothing ever works. Even saving the bad column to a memo or nvarchar(max) field doesn’t work. This week I found a work around.
These are the errors you might see:
Messages Error 0xc020901c: Data Flow Task: There was an error with output column "English String" (18) on output "Excel Source Output" (9). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task: The "output column "English String" (18)" failed because truncation occurred, and the truncation row disposition on "output column "English String" (18)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - Sheet1$" (1) returned error code 0xC020902A. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. (SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038. (SQL Server Import and Export Wizard)
Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. (SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039. (SQL Server Import and Export Wizard)

SOLUTION:

You can increase the number of rows sampled by increasing the value of TypeGuessRows under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel registry key and changing it to 0. This will force it to scan all rows. Even when working with 500k+ records this takes less than a second for me.
NOTE: Restart SQL Management studio after your change!
Screen shot below shows the location on my Windows 7 x64 box.