Technical Article

Smooshing String Function

,

Smooshing - The act of removing all non-alphanumeric characters from a string.

We have found several areas of our application where we need to perform cleaning on strings. The following is a SQL function which can be used to remove any non-alphanumeric characters.

To use the function create the script and then SELECT dbo.fn_SmooshIt('B?\RU.,C>}E') will result in 'Bruce' being selected.

CREATE FUNCTION dbo.fn_SmooshIt(@StripIt VARCHAR(100))

RETURNS VARCHAR(100)
AS
BEGIN

DECLARE @i INT,
@RetVal VARCHAR(100),
@Chr INT

-- If @RetVal = NULL the script won't work since NULL + 'C' = Null
-- So we default @RetVal =''. 
SELECT @RetVal='',@i=0

WHILE @i <= LEN(@StripIt)
BEGIN
-- 65 = A   90 = Z
        -- 97 = a  122 = z
        -- 48 = 0   57 = 9
SELECT @CHR=ASCII(SUBSTRING(@StripIt,@i,1))

IF ( (@CHR BETWEEN 65 AND 90) OR  (@CHR BETWEEN 97 AND 122) OR  (@CHR BETWEEN 48 AND 57))
BEGIN
SELECT @RetVal = @RetVal + CHAR(@CHR)
END

SET @i = @i+1

END
RETURN @RetVal
END

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating