CREATE ALTER FUNCTION dbo.udf_stripNonAlphaMumeric
(
@sVal Varchar (100)
)
RETURNS Char (100) AS
BEGIN
DECLARE @iCount Int
, @sNumVal varchar (100)
SELECT @iCount = 1, @sNumVal = ''
While @iCount <= Len (@sVal)
BEGIN
IF SUBSTRING (@sVal, @iCount, 1) LIKE '[A-Z,0-9]'
SET @sNumVal = @sNumVal + SUBSTRING (@sVal, @iCount, 1)
SET @iCount = @iCount + 1
END
SET @sNumVal = RIGHT (@sNumVal, 100)
RETURN @sNumVal
END

---Test
Select
'999 Lisbon Ave,P. O Box 10109, Little FallS Ext 2,1783' As Scenario1
,'999 Lisbon Ave P.O Box 10109 Little FallS Ext2 1783' As Scenario2
,Replace(dbo.udf_stripNonAlphaMumeric('*$#%6 &^*! 846 Lisbon Ave,P. O Box 10109, Little Fall Ext 2,1783'),',',' ') As Clean

I have addresses that I'm cleaning.
1.I need to remove Spaces (Some are double spaces..etc.. they should be replaced by single spaces)
2.Non alpha numeric characters

Now My problem is that the function cleans out the spaces too and I cant think of a
way of removing the non alphabetic, numeric characters and handle the spaces too
Leave out the single spaces and clean ot the double.

Initaily I thought of building a lookup table with Ascii values and then exclude the invalid ones
but i could intergrate it into the function.
ie..Look up table would be something like


IF (@AsciiValue BETWEEN 33 AND 47) OR (@AsciiValue BETWEEN 58 AND 64)
OR (@AsciiValue BETWEEN 58 AND 64) OR (@AsciiValue BETWEEN 91 AND 96)
OR (@AsciiValue BETWEEN 123 AND 255)

INSERT INTO #BADCODES VALUES(@AsciiValue)
......

Please could someone point me in the right direction.