Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Remove Control Characters in SQL string

Posted on 2013-06-05
4
3,690 Views
Last Modified: 2013-06-21
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
0
Comment
Question by:MECR123
  • 2
  • 2
4 Comments
 
LVL 7

Accepted Solution

by:
Ross Turner earned 300 total points
ID: 39221432
Try something like these

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

Open in new window

0
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39221440
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

0
 
LVL 9

Expert Comment

by:sarabhai
ID: 39221451
-------------------------------------------------------------------------------------------------------------
---- 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
 
LVL 9

Expert Comment

by:sarabhai
ID: 39221483
You need to use RemoveSpecialChars  function in your query like

select dbo.RemoveSpecialChars(Name) AS Name from Table1
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question