Developing Matt

My Technical Journal

Special Characters

leave a comment »

It might be hard to work with special characters in your data.  For instance, doing a replace on a field that looks like it has a blank (but is actually is carriage return) won’t yield the results you are seeking.  Instead, replace your special characters using the char function:

char(9) : tab

char(10): line feed

char(13) carriage return

If you want to find the others use this query:

SELECT ASCII( SUBSTRING(column_char,PATINDEX(‘%[^A-Z]%’,column_char),50))
,ASCII( SUBSTRING(column_char,PATINDEX(‘%[^A-Z]%’,column_char)+1,50))
FROM @tbl

Or if you are looking for a function I found this better approach from here by Christian d’Heureuse:

— 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, http://www.source-code.biz
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
         set @s2 = @s2 + char(@c)
      set @p = @p + 1
      end
   if len(@s2) = 0
      return null
   return @s2
   end

Thank you Christian for this tremendous help!  (script from http://www.source-code.biz/)

(I only copy the script here in case Christian decides to take down his website as this has happened to me in the past)

Advertisements

Written by matt

October 1, 2012 at 3:09 am

Posted in T-Sql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: