Solved

ASCII Function

Posted on 2008-10-20
8
731 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
 
LVL 1

Author Comment

by:jorbroni
ID: 22767373
This function still doesn't remove special characters.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now