Remove Control Characters in SQL string

hi

How do I remove control characters (such as carriage return) in a string in SQL 2005

my SQL is

Select Name from Table1

I wish to remove all control characters from the string 'Name'

thanks
Mike
MECR123Asked:
Who is Participating?
 
Ross TurnerManagement Information Support AnalystCommented:
Try something like these

Select 
replace(replace(name, CHAR(13), ''), CHAR(10), '')
from 
Table1

Open in new window

0
 
Ross TurnerManagement Information Support AnalystCommented:
This one removes all these:
Tab -> char(9)
Line feed -> char(10)
Carriage return -> char(13)

Select 
replace(replace(replace(name, CHAR(13), ''), CHAR(10), ''),char(9),'')
from 
Table1

Open in new window

1
 
sarabhaiCommented:
-------------------------------------------------------------------------------------------------------------
---- 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: sarabhai
-------------------------------------------------------------------------------------------------------------
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
0
 
sarabhaiCommented:
You need to use RemoveSpecialChars  function in your query like

select dbo.RemoveSpecialChars(Name) AS Name from Table1
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.