ASCII Function

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&



LVL 1
jorbroniAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Louis01Connect With a Mentor Commented:
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
 
Anthony PerkinsCommented:
>>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
 
jorbroniAuthor Commented:

>>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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Louis01Commented:

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
 
jorbroniAuthor Commented:
This function still doesn't remove special characters.
0
 
Louis01Commented:
What special characters (specifically) do you want to remove?
0
 
jorbroniAuthor Commented:
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
 
jorbroniAuthor Commented:
Brilliant!!!!
Thanks for the help Louis
0
All Courses

From novice to tech pro — start learning today.