Solved

ASCII Function

Posted on 2008-10-20
8
736 Views
Last Modified: 2012-05-05
I've created this function to get rid of all wild card chacters. The problem that I am having is that the only character it is getting rid of are the spaces. Could someone tell me what is wrong with this?

IF (SELECT OBJECT_ID('dbo.fnRemoveInvalidChar')) IS NOT NULL DROP FUNCTION dbo.fnRemoveInvalidChar
GO

CREATE FUNCTION dbo.fnRemoveInvalidChar(@col VARCHAR(8000), @replacementChar VARCHAR(8000) = ' ')
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE
@position INT,
@finalCol VARCHAR(8000)

SELECT @position = 1
SELECT @finalCol = ''

WHILE (@position <= DATALENGTH(@col))
BEGIN
IF (ASCII(SUBSTRING(@col, @position, 1))) >= 33
  BEGIN  
   SELECT @finalCol = @finalCol + SUBSTRING(@col, @position, 1)
  END
ELSE
  BEGIN
  SELECT @finalCol = @finalCol + @replacementChar
  END

SELECT @position = @position + 1
END

RETURN @finalCol
END


When I use it I get the following

SELECT dbo.fnRemoveInvalidChar('cow/bull&','')

Reults: cow/bull&



0
Comment
Question by:jorbroni
  • 4
  • 3
8 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22763180
>>I've created this function to get rid of all wild card chacters.<<
You mean control characters with ASCII = 0 - 31   ?

>>The problem that I am having is that the only character it is getting rid of are the spaces.<<
That would be because space is ASCII = 32
0
 
LVL 1

Author Comment

by:jorbroni
ID: 22763876

>>You mean control characters with ASCII = 0 - 31 <<
I'm aiming at 32 and up. The special characters

>>That would be because space is ASCII = 32<<
But in my if condition, I am telling the function to look at everything >= 33
0
 
LVL 11

Expert Comment

by:Louis01
ID: 22764743

CREATE FUNCTION dbo.fnRemoveInvalidChar(@InputString VARCHAR(8000), @replacementChar VARCHAR(8000) = ' ')
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @i INT
		  , @OutputString VARCHAR(8000)
 
	SET @OutputString = @InputString
 
	WHILE (@i < 32)
	 BEGIN
		SET @OutputString = REPLACE(@OutputString  COLLATE Latin1_General_BIN, CHAR(@i), @replacementChar)
	 END
 
	RETURN @OutputString
END

Open in new window

0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 1

Author Comment

by:jorbroni
ID: 22767373
This function still doesn't remove special characters.
0
 
LVL 11

Expert Comment

by:Louis01
ID: 22767379
What special characters (specifically) do you want to remove?
0
 
LVL 1

Author Comment

by:jorbroni
ID: 22768085
characters llike  ),(,<,>,&,/,\ ..etc

Example:
SELECT dbo.fnRemoveInvalidChar('cow/bull&','')

Results: cow/bull&
Desired results: cowbull

Sorry if I didn't call these characters the right name..

0
 
LVL 11

Accepted Solution

by:
Louis01 earned 250 total points
ID: 22768228
To remove any character except A to Z, a to z, 0 to 9 and space, use the code below:
CREATE FUNCTION dbo.fnRemoveInvalidChar(@col VARCHAR(8000), @replacementChar VARCHAR(8000) = ' ')
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE
	@position INT,
	@finalCol VARCHAR(8000),
	@thisChar CHAR
 
	SELECT @position = 1
	SELECT @finalCol = ''
 
	WHILE (@position <= DATALENGTH(@col))
	BEGIN
		SET @thisChar = SUBSTRING(@col, @position, 1)
		IF (ASCII(@thisChar) BETWEEN 48 AND 57) OR (ASCII(@thisChar) BETWEEN 65 AND 90) OR (ASCII(@thisChar) BETWEEN 97 AND 122) OR (ASCII(@thisChar) = 32)
		  BEGIN  
		   SELECT @finalCol = @finalCol + @thisChar
		  END
		ELSE
		  BEGIN
		  SELECT @finalCol = @finalCol + @replacementChar
		  END 
 
		SELECT @position = @position + 1
	END
 
	RETURN @finalCol
END

Open in new window

0
 
LVL 1

Author Closing Comment

by:jorbroni
ID: 31508080
Brilliant!!!!
Thanks for the help Louis
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

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 …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

685 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