Solved

Remove Control Characters in SQL string

Posted on 2013-06-05
4
3,378 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now