Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ASCII Function

Posted on 2008-10-20
8
Medium Priority
?
757 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 1000 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

Technology Partners: 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!

Question has a verified solution.

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

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 …
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

715 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