Solved

Remove Control Characters in SQL string

Posted on 2013-06-05
4
4,359 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
[X]
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
  • 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

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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

627 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