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