Solved

# ASCII Function

Posted on 2008-10-20
744 Views
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
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
• 4
• 3

LVL 75

Expert Comment

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

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

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
``````
0

LVL 1

Author Comment

ID: 22767373
This function still doesn't remove special characters.
0

LVL 11

Expert Comment

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

LVL 1

Author Comment

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

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
``````
0

LVL 1

Author Closing Comment

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

## Featured Post

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 …
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
###### Suggested Courses
Course of the Month8 days, 18 hours left to enroll