Function to find non-printable characters

,

I came across some non printable characters during an extract from a database. Some columns had control characters like TAB, BS, LF and so on. When these got into the extract they caused some downstream processes to fail because the TAB character was also used as a delimiter. I now have to figure out where these bad characters came from and eliminate them but I was able to quickly fix the feed by applying this function in my extract code.

I use the function in the following way

 

SELECT

CASE WHEN dbo.HasNonPrintableChar(column_1) = 0 THEN column_1 ELSE NULL END AS column_1

FROM table_1

 

I am now assured that I will only pass printable ASCII in the output of my query. The function uses the ASCII function and searches for values outside of the printable range. Enjoy!

CREATE FUNCTION dbo.HasNonPrintableChar (@input_string VARCHAR(MAX))
/* =============================================
-- Author:	Damien Alvarado
-- Create date: 2009-04-22
-- Email: damien.alvarado at parivedasolutions dot com
-- Description :The function checks the input string for non-printable characters like TAB, LF, BS and returns 1 if any are found, 0 otherwise
				
--Input parameters  @input_string VARCHAR(MAX), the string to search
--Output parameters :
--Return value : 0 --> No non-printable characters were found
                 1 --> Non-printable characters were found
			
			
 =============================================*/
RETURNS INTEGER
AS 
BEGIN
DECLARE @i INTEGER
DECLARE @string_length INTEGER
DECLARE @found_non_printable_character INTEGER
DECLARE @ascii_value INTEGER

SET @i = 1
SET @string_length = LEN(@input_string)
SET @found_non_printable_character = 0

--Check each character in the string for non-printable characters
WHILE @i <= @string_length AND @found_non_printable_character = 0
BEGIN
    SET @ascii_value = ASCII(SUBSTRING(@input_string,@i,1))

    --The range of printable ASCII characters is between 32 and 126
    IF  @ascii_value < 32 OR @ascii_value > 126 
    BEGIN
        SET @found_non_printable_character = 1
    END

    SET @i = @i + 1
END

RETURN @found_non_printable_character

END

Rate

4.5 (2)

Share

Share

Rate

4.5 (2)