Solved

ASCII Function

Posted on 2008-10-20
8
751 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
[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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

630 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