Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Remove Control Characters in SQL string

Posted on 2013-06-05
4
Medium Priority
?
4,993 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 1200 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

1
 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

971 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